Справочник по SQL¶
Содержание
Инструкция SELECT¶
Инструкция SELECT сканирует данные из таблиц и возвращают 0 или более строк.
Имена столбцов в запросах следует писать только строчными буквами.
Поддерживается следующий синтаксис запросов:
[WITH with_query [, …] ]
SELECT [ ALL | DISTINCT ] expression [, …]
[FROM from_item [, …] ]
[JOIN join_item [, …] ]
[WHERE condition ]
[GROUP BY grouping_element [, …] ]
[HAVING condition]
[FILTER (WHERE condition) ]
[UNION [ ALL | select ]
[ORDER BY expression [ ASC | DESC ][, …] ]
[LIMIT count ]
[CASE [expression] WHEN conditions [ELSE expression] END]
Оператор WITH¶
Оператор WITH позволяет именовать запросы и в дальнейшем ссылаться на них по имени.
WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;
Оператор SELECT¶
Пример:
SELECT a, b, a + b FROM table
В запрос можно добавлять квантификатор DISTINCT, чтобы возвращались только не совпадающие строки.
По умолчанию используется квантификатор ALL, который возвращает все строки.
SELECT DISTINCT person, age FROM employees
Оператор JOIN¶
Поддерживаются INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN,
FULL OUTER JOIN, NATURAL JOIN, CROSS JOIN.
Дальнейшие примеры основаны на этой таблице:
select * from x;
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1 | 2 |
+----------+----------+
INNER JOIN (простое соединение)¶
С помошью ключевых слов JOIN или INNER JOIN задается соединение, в которое входят только те строки, которые присутствуют в обеих таблицах.
select * from x inner join y ON x.column_1 = y.column_1;
+----------+----------+----------+----------+
| column_1 | column_2 | column_1 | column_2 |
+----------+----------+----------+----------+
| 1 | 2 | 1 | 2 |
+----------+----------+----------+----------+
LEFT OUTER JOIN¶
С помошью ключевых слов LEFT JOIN или LEFT OUTER JOIN задается соединение, в которое входят все строки из левой таблицы, даже если для них нет таких же строк в правой таблице.
Для строк, у которых нет совпадений в правой таблице, проставляются значения null в правой части итоговой таблицы.
select * from x left join y ON x.column_1 = y.column_2;
+----------+----------+----------+----------+
| column_1 | column_2 | column_1 | column_2 |
+----------+----------+----------+----------+
| 1 | 2 | | |
+----------+----------+----------+----------+
RIGHT OUTER JOIN¶
С помошью ключевых слов RIGHT JOIN или RIGHT OUTER JOIN задается соединение, в которое входят все строки из правой таблицы, даже если для них нет таких же строк в левой таблице.
Для строк, у которых нет совпадений в левой таблице, проставляются значения null в левой части итоговой таблицы.
select * from x right join y ON x.column_1 = y.column_2;
+----------+----------+----------+----------+
| column_1 | column_2 | column_1 | column_2 |
+----------+----------+----------+----------+
| | | 1 | 2 |
+----------+----------+----------+----------+
FULL OUTER JOIN¶
С помошью ключевых слов FULL JOIN или FULL OUTER JOIN задается соединение, в которое входят сразу все результаты LEFT OUTER JOIN и RIGHT OUTER JOIN.
В него попадают все строки из этих двух соединений, а для строк, у которых нет нет совпадений в другой таблице, проставляются значения null в соответствующей части итоговой таблицы.
select * from x full outer join y ON x.column_1 = y.column_2;
+----------+----------+----------+----------+
| column_1 | column_2 | column_1 | column_2 |
+----------+----------+----------+----------+
| 1 | 2 | | |
| | | 1 | 2 |
+----------+----------+----------+----------+
NATURAL JOIN (неявное соединение)¶
Оператор NATURAL JOIN позволяет объединить таблицы по принципу INNER JOIN, но здесь объединение выполняется на основании общего столбца (или столбцов) этих таблиц.
Если у таблиц нет общего столбца (с одинаковым именем), то результат получается аналогичен CROSS JOIN.
select * from x natural join y;
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1 | 2 |
+----------+----------+
CROSS JOIN¶
Оператор CROSS JOIN позволяет получить декартово произведение нескольких таблиц. В результате создается набор, включающий все возможные комбинации строк в таблицах.
Декартово произведение в данном случае – это результат соединения строки из первой таблицы с каждой строкой из второй таблицы.
select * from x cross join y;
+----------+----------+----------+----------+
| column_1 | column_2 | column_1 | column_2 |
+----------+----------+----------+----------+
| 1 | 2 | 1 | 2 |
+----------+----------+----------+----------+
CROSS JOIN особенно полезен, когда между таблицами нет определенной связи, и вам нужно создать полную комбинацию записей из каждой таблицы.
Оператор GROUP BY¶
Оператор GROUP BY используется в SELECT-запросах для сбора данных по нескольким записям и группировки результатов по одному или нескольким столбцам.
Пример:
SELECT a, b, MAX(c) FROM table GROUP BY a, b
Некоторые агрегатные функции могут принимать на вход необязательные условия упорядочения, например ARRAY_AGG. Если такое условие задано, то результат агрегации рассчитывается в порядке, заданном этим условием.
Пример:
SELECT a, b, ARRAY_AGG(c ORDER BY d) FROM table GROUP BY a, b
Оператор HAVING¶
Оператор HAVING используется в сочетании с оператором GROUP BY, чтобы в результат попали только те строки, для которых выполняется некое условие.
Пример:
SELECT a, b, MAX(c) FROM table GROUP BY a, b HAVING MAX(c) > 10
Модификатор FILTER¶
Модификатор FILTER используется после агрегатной функции, например SUM, AVG, COUNT, ARRAY_AGG, LIST_AGG и т.д. В результат агрегации попадают только те строки, которые удовлетворяют условию WHERE.
Для колонок, не участвующих в агрегации, используется оператор GROUP BY.
Пример:
SELECT a, b, COUNT(c) FILTER(WHERE c > 10) AS `over 10` FROM table GROUP BY a, b
Примечание
В текущей версии TCS модификатор FILTER не позволяет использовать параметризацию.
Оператор UNION¶
Оператор UNION используется для объединения результирующих наборов из 2 или более операторов SELECT. Он удаляет повторяющиеся строки между различными операторами SELECT.
Каждый оператор SELECT в операторе UNION должен иметь одинаковое количество полей в наборах результатов с одинаковыми типами данных.
Пример:
SELECT
a,
b,
c
FROM table1
UNION ALL
SELECT
a,
b,
c
FROM table2
Оператор ORDER BY¶
Оператор ORDER BY позволяет упорядочить результат в соответствии с заданным условием.
По умолчанию упорядочивание осуществляется в порядке убывания (ASC). Также можно задать упорядочивание в порядке возрастания, указав DESC в конце запроса с ORDER BY.
Примеры:
SELECT age, person FROM table ORDER BY age;
SELECT age, person FROM table ORDER BY age DESC;
SELECT age, person FROM table ORDER BY age, person DESC;
Примечание
В текущей версии TCS выражение c оператором ORDER BY всегда использует сортировку для формирования выборки. Это делается в любом случае, даже если используемый для чтения индекс предоставляет кортежи в запрашиваемом порядке.
При необходимости можно явно задать для планировщика нужный индекс с помощью функции plan_with()
с аргументом use_index, не используя в запросе оператор ORDER BY.
Оператор LIMIT¶
Оператор LIMIT используется для ограничения количества возвращаемых записей на основе предельного значения.
Ограничение задается неотрицательным целым числом.
Пример:
SELECT age, person FROM table LIMIT 10
Оператор CASE¶
Оператор CASE используется для проверки ряда условий. Он работает аналогично выражению if-then-else:
как только одно из условий оказывается выполнено, дальнейшее чтение данных и проверки прекращаются и возвращается результат, указанный для этого условия;
если ни одно из условий не выполнено, то возвращается значение предложения ELSE;
если не выполнено ни одно условие и в запросе не предусмотрено предложение ELSE, то возвращается NULL.
Поддерживается следующий синтаксис запросов:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
где:
expression(необязательно) - значение, которое сравнивается с условиями.condition_1 .. condition_n- проверяемые условия. Все условия должны быть одного типа данных. Условия проверяются по порядку, одно за другим.result_1 .. result_n- результаты, один из которых возвращаются, если соответствующее условие оказывается выполнено. Все результаты должны быть одного типа данных.
Пример:
SELECT person, city, country
FROM table
ORDER BY
(CASE
WHEN city IS NULL THEN country
ELSE city
END);
В этом запросе мы упорядочиваем список людей по названию города. Если же город для какого-то человека не указан, то упорядочивание ведется по названию страны.
Примечание
В текущей версии TCS оператор CASE не позволяет использовать параметризацию.
Например, такое выражение сейчас не поддерживается:
PREPARE PREP1(STRING) AS
SELECT person, city, country
FROM table
ORDER BY
(CASE
WHEN city IS NULL THEN $1
ELSE city
END);
Инструкции PREPARE / EXECUTE¶
Инструкция PREPARE позволяет создать и сохранить аналитический расчет – инструкцию SQL с аргументами-заполнителями (placeholders). Затем такой аналитический расчет можно эффективно выполнять повторно с помощью инструкции EXECUTE.
Пример:
Создадим аналитический расчет greater_than, который выбирает все записи,
в которых столбец a больше, чем заданный параметр:
PREPARE greater_than(INT) AS SELECT * FROM example WHERE a > $1;
Затем этот аналитический расчет можно выполнять с разными значениями параметра:
EXECUTE greater_than(20);
EXECUTE greater_than(100);
Предполагаемые типы¶
Если тип параметра не указан, он может быть определен во время выполнения.
Пример:
PREPARE greater_than AS SELECT * FROM example WHERE a > $1;
EXECUTE greater_than(20);
Позиционные аргументы¶
В случае нескольких параметров аналитические расчеты могут использовать позиционные аргументы.
Пример:
PREPARE greater_than(INT, DOUBLE) AS SELECT * FROM example WHERE a > $1 AND b > $2;
EXECUTE greater_than(20, 23.3);
Подзапросы¶
Поддерживаются подзапросы с предикатами EXISTS, NOT EXISTS, IN, NOT IN,
а также скалярные подзапросы.
Дальнейшие примеры основаны на этой таблице:
select * from x;
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1 | 2 |
+----------+----------+
Предикат EXISTS¶
EXISTS используется для того, чтобы вернуть все строки, для которых коррелированный подзапрос находит одно и более совпадений.
Поддерживаются только коррелированные подзапросы.
select * from y where exists (select * from x where x.column_1 = y.column_1);
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1 | 2 |
+----------+----------+
1 row in set.
Предикат NOT EXISTS¶
NOT EXISTS используется для того, чтобы вернуть все строки, для которых коррелированный подзапрос не находит ни одного совпадения.
Поддерживаются только коррелированные подзапросы.
SELECT * FROM y WHERE NOT EXISTS (SELECT * FROM x WHERE x.column_1 = y.column_1);
0 rows in set.
Предикат IN¶
IN используется для того, чтобы вернуть все строки, для которых в результатах коррелированного подзапроса нашлось значение некоторого выражения.
select * from x where column_1 in (select column_1 from y);
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1 | 2 |
+----------+----------+
1 row in set.
Предикат NOT IN¶
NOT IN используется для того, чтобы вернуть все строки, для которых в результатах коррелированного подзапроса не нашлось значение некоторого выражения.
SELECT * FROM x WHERE column_1 NOT IN (SELECT column_1 FROM y);
0 rows in set.
Скалярные подзапросы¶
С помощью скалярного подзапроса можно получить некое значение, которое можно далее использовать в запросе много раз в разных контекстах.
Поддерживаются только коррелированные подзапросы.
Далее приводится пример с фильтром, в котором используется скалярный подзапрос.
select * from x y where column_1 < (select sum(column_2) from x where x.column_1 = y.column_1);
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1 | 2 |
+----------+----------+
1 row in set.
Команды DDL¶
Правила именования объектов¶
Имена объектов (таблиц, столбцов, индексов, представлений для чтения, аналитических расчетов) должны быть валидными строками формата utf8.
Если имя содержит заглавные буквы или пробелы, то его следует задавать в двойных кавычках.
В остальных случаях кавычки не требуются. Например: name, "name 1", "Name", "NAME".
Максимальная длина имени – 128 символов.
CREATE TABLE¶
Команда CREATE TABLE создает таблицу с указанным именем на основе заданного списка полей (column_def).
Поддерживается следующий синтаксис запросов:
CREATE TABLE [IF NOT EXISTS] table_name (
[
{ column_def },
[, ...]
]
[MAX_ROWS number])
где:
column_def = column_name data_type [CAPACITY x]
Если таблица существует и указан флаг IF NOT EXISTS, но при этом column_def отличается
от существующего, то существующая таблица не меняется и возвращается HTTP-ответ 200 OK
с текстом EXIST.
CREATE TABLE t(a i32, b utf8)
CREATE TABLE IF NOT EXISTS t(
a i32 CAPACITY 10000,
b utf8,
c u64,
d bool
)
В данном примере сначала создается таблица t с полями a i32, b utf8.
Затем, после запроса с IF EXISTS, возвращается HTTP-ответ 200 OK с текстом EXIST,
а таблица t не меняется.
Параметр MAX_ROWS позволяет задать максимальное количество записей в таблице. См. подробнее Вытеснение данных.
CREATE EXTERNAL TABLE¶
Оператор SQL CREATE EXTERNAL TABLE регистрирует местоположение в локальной файловой системе или удаленном хранилище объектов в виде именованной таблицы, к которой можно обращаться с запросами.
Поддерживается следующий синтаксис запросов:
CREATE [UNBOUNDED] EXTERNAL TABLE
[ IF NOT EXISTS ]
<TABLE_NAME>[ (<column_definition>) ]
STORED AS <file_type>
[ PARTITIONED BY (<column list>) ]
[ WITH ORDER (<ordered column list>) ]
[ OPTIONS (<key_value_list>) ]
LOCATION <literal>
где:
<column_definition> := (<column_name> <data_type>, ...)<column_list> := (<column_name>, ...)<ordered_column_list> := (<column_name> <sort_clause>, ...)<key_value_list> := (<literal> <literal, <literal> <literal>, ...)
OPTIONS задает параметры записи.
Тип файла может быть любым: CSV, ARROW, PARQUET, AVRO или JSON.
LOCATION <literal> указывает местоположение для поиска данных.
Это может быть путь к файлу или каталогу секционированных файлов, локальный
или в хранилище объектов.
Пример: Parquet
Источники данных Parquet можно зарегистрировать с помощью SQL-инструкции CREATE EXTERNAL TABLE (см. пример ниже). Предоставлять информацию о схеме необязательно.
CREATE EXTERNAL TABLE taxi
STORED AS PARQUET
LOCATION '/mnt/nyctaxi/tripdata.parquet';
По умолчанию при создании таблицы TCS считывает файлы для сбора статистики.
Это может быть дорогостоящей операцией, но также может существенно ускорить
выполнение последующих запросов. Если вам не нужно собирать статистику при создании таблицы,
то перед созданием таблицы установите значение false для переменной
datafusion.execution.collect_statistics.
Пример: CSV
Источники данных CSV можно зарегистрировать с помощью SQL-инструкции CREATE EXTERNAL TABLE. Схема будет определена на основе сканирования подмножества файла.
CREATE EXTERNAL TABLE test
STORED AS CSV
LOCATION '/path/to/aggregate_simple.csv'
OPTIONS ('has_header' 'true');
Пример: сжатие
Можно использовать сжатые файлы, например .csv.gz:
CREATE EXTERNAL TABLE test
STORED AS CSV
LOCATION '/path/to/directory/of/files'
OPTIONS ('has_header' 'true');
Пример: указание схемы
Схему можно указывать вручную.
CREATE EXTERNAL TABLE test (
c1 VARCHAR NOT NULL,
c2 INT NOT NULL,
c3 SMALLINT NOT NULL,
c4 SMALLINT NOT NULL,
c5 INT NOT NULL,
c6 BIGINT NOT NULL,
c7 SMALLINT NOT NULL,
c8 INT NOT NULL,
c9 BIGINT NOT NULL,
c10 VARCHAR NOT NULL,
c11 FLOAT NOT NULL,
c12 DOUBLE NOT NULL,
c13 VARCHAR NOT NULL
)
STORED AS CSV
LOCATION '/path/to/aggregate_test_100.csv'
OPTIONS ('has_header' 'true');
Пример: секционированные таблицы
Можно указать каталог, содержащий секционированную таблицу (несколько файлов с одинаковой схемой).
CREATE EXTERNAL TABLE test
STORED AS CSV
LOCATION '/path/to/directory/of/files'
OPTIONS ('has_header' 'true');
Пример: неограниченные источники данных
Можно создавать неограниченные источники данных с помощью SQL-инструкции CREATE UNBOUNDED EXTERNAL TABLE.
CREATE UNBOUNDED EXTERNAL TABLE taxi
STORED AS PARQUET
LOCATION '/mnt/nyctaxi/tripdata.parquet';
Этот оператор фактически считывает данные из файла фиксированного размера, поэтому наиболее удачным примером здесь может служить чтение из файла FIFO. Тем не менее, как только TCS видит ключевое слово UNBOUNDED в источнике данных, он пытается выполнить запросы, которые ссылаются на этот неограниченный источник, в потоковом режиме. Если это невозможно в соответствии со спецификациями запроса, создание плана завершается ошибкой, указывающей на невозможность выполнения данного запроса в потоковом режиме.
Запросы, которые могут выполняться с неограниченными источниками (т.е. в потоковом режиме), являются подмножеством запросов, которые могут выполняться с ограниченными источниками. Запрос, который не выполняется с неограниченным источником (источниками), может сработать с ограниченным источником (источниками).
Пример: предложение WITH ORDER
При создании выходных данных из источника данных, который уже упорядочен некоторым выражением, можно предварительно указать порядок данных с помощью предложения WITH ORDER. Это применимо даже в том случае, если выражение, используемое для сортировки, является сложным, что дает большую гибкость. Например:
CREATE EXTERNAL TABLE test (
c1 VARCHAR NOT NULL,
c2 INT NOT NULL,
c3 SMALLINT NOT NULL,
c4 SMALLINT NOT NULL,
c5 INT NOT NULL,
c6 BIGINT NOT NULL,
c7 SMALLINT NOT NULL,
c8 INT NOT NULL,
c9 BIGINT NOT NULL,
c10 VARCHAR NOT NULL,
c11 FLOAT NOT NULL,
c12 DOUBLE NOT NULL,
c13 VARCHAR NOT NULL
)
STORED AS CSV
WITH ORDER (c2 ASC, c5 + c8 DESC NULL FIRST)
LOCATION '/path/to/aggregate_test_100.csv'
OPTIONS ('has_header' 'true');
Здесь в предложении WITH ORDER указывается порядок сортировки:
WITH ORDER (sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
[, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...])
Важно
Использование предложения WITH ORDER в инструкции CREATE EXTERNAL TABLE указывает только порядок, в котором данные должны считываться из внешнего файла. Если данные в файле еще не отсортированы в соответствии с указанным порядком, результаты могут быть неверными.
Предложение WITH ORDER не влияет на порядок данных в исходном внешнем файле.
Если источники данных уже разделены в стиле Hive, то для сокращения разделов можно использовать параметр PARTITIONED BY.
/mnt/nyctaxi/year=2022/month=01/tripdata.parquet
/mnt/nyctaxi/year=2021/month=12/tripdata.parquet
/mnt/nyctaxi/year=2021/month=11/tripdata.parquet
CREATE EXTERNAL TABLE taxi
STORED AS PARQUET
PARTITIONED BY (year, month)
LOCATION '/mnt/nyctaxi';
DROP TABLE¶
Команда DROP TABLE удаляет таблицу с указанным именем.
Поддерживается следующий синтаксис запросов:
DROP TABLE [IF EXISTS] table_name
Параметры:
IF EXISTS – не считать ошибкой, если таблица не существует.
table_name (обязательный) - имя удаляемой таблицы.
ALTER TABLE¶
Поддерживается следующий синтаксис запросов:
Добавление столбцов:
ALTER TABLE [IF EXISTS] table_name ADD COLUMN column1 column2Удаление столбцов:
ALTER TABLE [IF EXISTS] table_name DROP COLUMN column1 column2Переименование столбцов:
ALTER TABLE [IF EXISTS] table_name RENAME COLUMN column2 TO column3Переименование таблицы:
ALTER TABLE [IF EXISTS] table_name RENAME TO new_table_nameИзменение максимального количества хранимых записей в таблице:
ALTER TABLE table_name MAX_ROWS number
Примечание
При удалении столбцов из середины таблицы с помощью DROP COLUMN нужно учитывать, что нельзя удалить столбец, если после него идут какие-либо индексированные столбцы, входящие в какой-либо индекс.
Примечание
Текущая версия TCS не поддерживает запросы на изменение типов данных в столбцах:
ALTER TABLE [IF EXISTS] table_name ALTER COLUMN column1 TYPE ....
CREATE VIEW¶
Нематериализованное SQL-представление (non-materialized view in SQL) – это виртуалная таблица с результатами SQL-запроса. Ее можно создать из существующей таблицы или из списка значений.
Поддерживается следующий синтаксис запросов:
CREATE [ OR REPLACE ] VIEW view_name AS statement;
Примеры:
Создание представления из списка значений:
CREATE TABLE users AS VALUES(1,2),(2,3),(3,4),(4,5); CREATE VIEW test AS SELECT column1 FROM users; SELECT * FROM test; +---------+ | column1 | +---------+ | 1 | | 2 | | 3 | | 4 | +---------+ CREATE VIEW test AS VALUES(1,2),(5,6); SELECT * FROM test; +---------+---------+ | column1 | column2 | +---------+---------+ | 1 | 2 | | 5 | 6 | +---------+---------+
Создание представления из существующей таблицы:
CREATE VIEW test AS SELECT * FROM my_table WHERE my_table.a = 1;
Изменение существующего представления:
CREATE OR REPLACE VIEW test AS SELECT * FROM my_table WHERE my_table.a = 2;
DROP VIEW¶
Команда DROP VIEW удаляет нематериализованное SQL-представление из каталога TCS.
Поддерживается следующий синтаксис запросов:
DROP VIEW [ IF EXISTS ] view_name;
Параметры:
IF EXISTS – не считать ошибкой, если представление не существует.
view_name (обязательный) - имя удаляемого представления.
CREATE INDEX¶
Команда CREATE INDEX создает индекс (первичный или вторичный) по одной или нескольким колонкам в таблице.
Поддерживается следующий синтаксис запросов:
CREATE INDEX [ SYNC ] [ IF NOT EXISTS ] index_name ON table_name({ column_name })
где:
CREATE INDEX (без SYNC) создает индекс в асинхронном режиме. Если флаг SYNC не указан, то ответ на запрос возвращается быстро, поскольку TCS не ждет построения индекса и выполняет такой запрос с минимумом проверок (имя индекса уникально, таблица существует, указаны только уникальные колонки и т.д.). Вместе с ответом возвращается номер
id, по которому можно отследить успешность операции CREATE INDEX с помощью следующего запроса (выборка из служебной таблицыsystem.index.build):SELECT status FROM system.index.build WHERE id={id}
В данной таблице содержится информация про запущенные операции построения индексов. В текущей версии TCS эта таблица пока не реплицируется и не является персистентной: если до момента построения индекса происходит отказ экземпляра хранилища, то после перезапуска экземпляра информация о данном построении не сохранится.
CREATE INDEX SYNC создает индекс в синхронном режиме. Если флаг SYNC указан, то ответ на запрос не возвращается, пока не будет построен индекс.
Если индекс существует и указан флаг IF NOT EXISTS, то существующий индекс не меняется и возвращается HTTP-ответ
200 OKс текстомEXIST.
Пример (создание составного индекса по 3 столбцам в синхронном режиме):
CREATE INDEX SYNC test_index ON departments(name, manager, size);
Примечание
В текущей версии TCS индексы нельзя строить по колонкам, имеющим тип данных с плавающей точкой.
DROP INDEX¶
Команда DROP INDEX удаляет индекс из каталога TCS.
Поддерживается следующий синтаксис запросов:
DROP INDEX [ IF EXISTS ] index_name;
Параметры:
IF EXISTS – не считать ошибкой, если индекс не существует.
index_name (обязательный) - имя удаляемого индекса.
ALTER INDEX¶
Текущая версии TCS пока не поддерживает возможность изменения индексов.
Команды DML¶
COPY¶
Копирует содержимое таблицы или запроса в файл(ы).
Поддерживается следующий синтаксис запросов:
COPY { 'table_name' | (query) }
TO 'file_name'
[ STORED AS format ]
[ PARTITIONED BY (column_name [, ...]) ]
[ OPTIONS( option [, ... ] ) ]
STORED AS задает формат файла, который будет записан командой COPY.
Поддерживаемые форматы файлов: parquet, csv, json и arrow.
Если это условие не задано, то формат выводится по возможности из расширения файла
(например, res.parquet подразумевает формат parquet).
PARTITIONED BY задает столбцы, которые будут использоваться для разбиения
выходных файлов на отдельные каталоги. По умолчанию столбцы,
используемые в PARTITIONED BY, будут удалены из выходного формата.
Если нужно сохранить столбцы, то следует указать параметр записи
execution.keep_partition_by_columns = true.
OPTIONS задает параметры записи.
Примеры:
копирование из таблицы:
COPY 'attributes' TO 'res' STORED AS json PARTITIONED BY (x);
копирование из запроса:
COPY (SELECT x FROM attributes) TO 'res' STORED AS json;
INSERT¶
Вставка значений в таблицу.
Поддерживается следующий синтаксис запросов:
INSERT INTO table_name { VALUES ( expression [, ...] ) [, ...] | query }
Пример:
INSERT INTO target_table VALUES (1, 'Foo'), (2, 'Bar');
+-------+
| count |
+-------+
| 2 |
+-------+
См. также Вставка данных.
UPDATE¶
Обновление данных в таблице.
Поддерживается следующий синтаксис запросов:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
Пример:
UPDATE names
SET name = 'Anna Danilova', age = '32'
WHERE id = 10
См. также Обновление данных.
DELETE¶
Удаление значений из таблицы.
Поддерживается следующий синтаксис запросов:
DELETE FROM table_name WHERE condition LIMIT value
Пример:
INSERT INTO a VALUES (1),(2),(3),(4)
count
0 4
SELECT * FROM a
i
0 1
1 2
2 3
3 4
DELETE FROM a WHERE i>1
SELECT * FROM a
i
0 1
См. также Удаление данных.
Инструкция EXPLAIN¶
Команда EXPLAIN показывает логический и физический план выполнения указанного
оператора SQL.
Поддерживается следующий синтаксис запросов:
EXPLAIN [ANALYZE] [VERBOSE] statement
Пример:
EXPLAIN SELECT SUM(x) FROM table GROUP BY b;
EXPLAIN¶
Отображает план выполнения указанного SQL-выражения.
Если нужно получить больше подробностей, используйте EXPLAIN VERBOSE.
EXPLAIN SELECT SUM(x) FROM table GROUP BY b;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | Projection: #SUM(table.x) |
| | Aggregate: groupBy=[[#table.b]], aggr=[[SUM(#table.x)]] |
| | TableScan: table projection=[x, b] |
| physical_plan | ProjectionExec: expr=[SUM(table.x)@1 as SUM(table.x)] |
| | AggregateExec: mode=FinalPartitioned, gby=[b@0 as b], aggr=[SUM(table.x)] |
| | CoalesceBatchesExec: target_batch_size=4096 |
| | RepartitionExec: partitioning=Hash([Column { name: "b", index: 0 }], 16) |
| | AggregateExec: mode=Partial, gby=[b@1 as b], aggr=[SUM(table.x)] |
| | RepartitionExec: partitioning=RoundRobinBatch(16) |
| | CsvExec: file_groups={1 group: [[/tmp/table.csv]]}, projection=[x, b], has_header=false |
| | |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN ANALYZE¶
Отображает план выполнения указанного SQL-выражения, а также метрики.
Если нужно получить больше подробностей, используйте EXPLAIN ANALYZE VERBOSE.
EXPLAIN ANALYZE SELECT SUM(x) FROM table GROUP BY b;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan with Metrics | GlobalLimitExec: skip=0, fetch=5000, metrics=[output_rows=1, elapsed_compute=28ns] |
| | LocalLimitExec: fetch=5000, metrics=[output_rows=1, elapsed_compute=156ns] |
| | AggregateExec: mode=Final, gby=[], aggr=[MAX(attributes.Attribute1), MAX(attributes.Attribute2)], metrics=[output_rows=1, elapsed_compute=385.384µs] |
| | CoalescePartitionsExec, metrics=[output_rows=2, elapsed_compute=2.757µs] |
| | AggregateExec: mode=Partial, gby=[], aggr=[MAX(attributes.Attribute1), MAX(attributes.Attribute2)], metrics=[output_rows=2, elapsed_compute=915.989µs] |
| | ProjectionExec: expr=[Attribute1@1 as Attribute1, Attribute2@2 as Attribute2], metrics=[output_rows=1199, elapsed_compute=58.884µs] |
| | CoalesceBatchesExec: target_batch_size=8192, metrics=[output_rows=1199, elapsed_compute=7.723086ms] |
| | FilterExec: IndexAttributeInt0@0 = 55, metrics=[output_rows=1199, elapsed_compute=2.430824ms] |
| | UnionExec, metrics=[output_rows=1199, elapsed_compute=5.251683ms] |
| | TarantoolExec: fields=[IndexAttributeInt0, Attribute1, Attribute2], fields_count=2, arrow_select=true, |
| | index_seek=[column=tcs.public.attributes.IndexAttributeInt0, value=55] inexact_filters=IndexAttributeInt0 = Int32(55), |
| | metrics=[output_rows=0, elapsed_compute=287ns, input_batches=0, record_batch_counter=0, errors=0, max_index_size=0, max_empty_chunks=0] |
| | BufExec: fields=[IndexAttributeInt0, Attribute1, Attribute2], fields_count=2 |
| | index_seek=[column=tcs.public.attributes.IndexAttributeInt0, value=55], |
| | metrics=[output_rows=1199, elapsed_compute=46.022µs, input_batches=0, record_batch_counter=15, errors=0, max_index_size=0, max_empty_chunks=0] |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Последовательности¶
Оператор CREATE SEQUENCE¶
Оператор для создания генератора последовательностей.
Поддерживаются только последовательности целочисленных значений i64.
Синтаксис:
CREATE SEQUENCE [ IF NOT EXISTS ] имя
[ INCREMENT [ BY ] шаг ]
[ MINVALUE мин_значение | NO MINVALUE ]
[ MAXVALUE макс_значение | NO MAXVALUE ]
[ START [ WITH ] начало ]
[ CACHE кеш ]
[ [ NO ] CYCLE ]
Параметры:
IF NOT EXISTS – не считать ошибкой, если последовательность с таким именем уже существует.
имя (обязательный) – имя создаваемой последовательности.
шаг – шаг последовательности. Это число, которое будет добавляться к текущему значению последовательности для получения нового значения. С положительным шагом последовательность будет возрастающей, а с отрицательным — убывающей. Значение по умолчанию: 1.
мин_значение – наименьшее число, которое будет генерировать последовательность. Если параметр не указан, либо указано NO MINVALUE, то используется значение по умолчанию: 1 для возрастающей последовательности, либо минимальное значение типа данных – для убывающей.
макс_значение – наибольшее число, которое будет генерировать последовательность. Если параметр не указан, либо указано NO MAXVALUE, то используется значение по умолчанию: максимальное значение типа данных для возрастающей последовательности, либо -1 – для убывающей.
начало – начальное значение последовательности. Позволяет запустить последовательность с любого значения. По умолчанию началом считается мин_значение для возрастающих последовательностей и макс_значение для убывающих.
кеш – определяет, сколько чисел последовательности будет выделяться и сохраняться в памяти для ускорения доступа к ним.
Примечание
Параметр
cacheможно задавать, но в текущей версии TCS он ни на что не влияет.CYCLE – позволяет зациклить последовательность при достижении макс_значение или мин_значение для возрастающей или убывающей последовательности, соответственно. Когда этот предел достигается, следующим числом этих последовательностей будет соответственно мин_значение или макс_значение.
Если указывается NO CYCLE, то при каждом вызове
nextval()после достижения предельного значения будет возникать ошибка. Если CYCLE или NO CYCLE не указаны, то по умолчанию предполагается NO CYCLE.
Важно
Все параметры оператора CREATE SEQUENCE необходимо указывать в описанном выше порядке.
Обращаться к созданной последовательности можно из запросов на чтение, вставку и обновление данных.
Пример:
CREATE SEQUENCE IF NOT EXISTS my_seq INCREMENT BY 5 MINVALUE 15 MAXVALUE 2000 START WITH 20 CACHE 5 CYCLE
Оператор DROP SEQUENCE¶
Оператор для удаления генератора последовательностей.
Поддерживается следующий синтаксис запросов:
DROP SEQUENCE [IF EXISTS] seq_name
Параметры:
IF EXISTS – не считать ошибкой, если последовательность не существует.
seq_name (обязательный) - имя удаляемой последовательности.
Функция nextval()¶
Продвигает последовательность к следующему значению и возвращает его.
nextval('name')
Аргументы:
name(обязательный) – имя последовательности.
Операторы¶
Числовые операторы¶
+(plus)-(minus)*(multiply)/(divide)%(modulo)
Оператор +¶
Сложение:
> SELECT 1 + 2;
+---------------------+
| Int64(1) + Int64(2) |
+---------------------+
| 3 |
+---------------------+
Оператор -¶
Вычитание:
> SELECT 4 - 3;
+---------------------+
| Int64(4) - Int64(3) |
+---------------------+
| 1 |
+---------------------+
Оператор *¶
Умножение:
> SELECT 2 * 3;
+---------------------+
| Int64(2) * Int64(3) |
+---------------------+
| 6 |
+---------------------+
Оператор /¶
Деление (результат деления целых чисел округляется в сторону нуля):
> SELECT 8 / 4;
+---------------------+
| Int64(8) / Int64(4) |
+---------------------+
| 2 |
+---------------------+
Оператор %¶
Значение по модулю (остаток):
> SELECT 7 % 3;
+---------------------+
| Int64(7) % Int64(3) |
+---------------------+
| 1 |
+---------------------+
Операторы сравнения¶
=(равно)!=(не равно)<(меньше чем)<=(меньше или равно)>(больше чем)>=(больше или равно)IS DISTINCT FROM(отличается от)IS NOT DISTINCT FROM(не отличается от)~(удовлетворяет условиям регулярного выражения, с учетом регистра)~*(удовлетворяет условиям регулярного выражения, без учета регистра)!~(не удовлетворяет условиям регулярного выражения, с учетом регистра)!~*(не удовлетворяет условиям регулярного выражения, без учета регистра)
Оператор =¶
Равно:
> SELECT 1 = 1;
+---------------------+
| Int64(1) = Int64(1) |
+---------------------+
| true |
+---------------------+
Оператор !=¶
Не равно:
> SELECT 1 != 2;
+----------------------+
| Int64(1) != Int64(2) |
+----------------------+
| true |
+----------------------+
Оператор <¶
Меньше чем:
> SELECT 3 < 4;
+---------------------+
| Int64(3) < Int64(4) |
+---------------------+
| true |
+---------------------+
Оператор <=¶
Меньше или равно:
> SELECT 3 <= 3;
+----------------------+
| Int64(3) <= Int64(3) |
+----------------------+
| true |
+----------------------+
Оператор >¶
Больше чем:
> SELECT 6 > 5;
+---------------------+
| Int64(6) > Int64(5) |
+---------------------+
| true |
+---------------------+
Оператор >=¶
Больше или равно:
> SELECT 5 >= 5;
+----------------------+
| Int64(5) >= Int64(5) |
+----------------------+
| true |
+----------------------+
Оператор IS DISTINCT FROM¶
Отличается от:
> SELECT 0 IS DISTINCT FROM NULL;
+--------------------------------+
| Int64(0) IS DISTINCT FROM NULL |
+--------------------------------+
| true |
+--------------------------------+
Гарантирует, что результатом сравнения является истина или ложь, а не пустое множество.
Оператор IS NOT DISTINCT FROM¶
Этот оператор является отрицанием оператора IS DISTINCT FROM
Не отличается от:
> SELECT NULL IS NOT DISTINCT FROM NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true |
+--------------------------------+
Оператор ~¶
Удовлетворяет условиям регулярного выражения (с учетом регистра):
> SELECT 'tarantool' ~ '^tarantool(-cli)*';
+-------------------------------------------------+
| Utf8("tarantool") ~ Utf8("^tarantool(-cli)*") |
+-------------------------------------------------+
| true |
+-------------------------------------------------+
Оператор ~*¶
Удовлетворяет условиям регулярного выражения (без учета регистра):
> SELECT 'tarantool' ~* '^TARANTOOL(-cli)*';
+--------------------------------------------------+
| Utf8("tarantool") ~* Utf8("^TARANTOOL(-cli)*") |
+--------------------------------------------------+
| true |
+--------------------------------------------------+
Оператор !~¶
Не удовлетворяет условиям регулярного выражения (с учетом регистра):
> SELECT 'tarantool' !~ '^TARANTOOL(-cli)*';
+--------------------------------------------------+
| Utf8("tarantool") !~ Utf8("^TARANTOOL(-cli)*") |
+--------------------------------------------------+
| true |
+--------------------------------------------------+
Оператор !~*¶
Не удовлетворяет условиям регулярного выражения (без учета регистра):
> SELECT 'tarantool' !~* '^TARANTOOL(-cli)+';
+---------------------------------------------------+
| Utf8("tarantool") !~* Utf8("^TARANTOOL(-cli)+") |
+---------------------------------------------------+
| true |
+---------------------------------------------------+
Логические операторы¶
AND (логическое И)
OR (логическое ИЛИ)
Оператор AND¶
Логическое И:
> SELECT true AND true;
+---------------------------------+
| Boolean(true) AND Boolean(true) |
+---------------------------------+
| true |
+---------------------------------+
Оператор OR¶
Логическое ИЛИ:
> SELECT false OR true;
+---------------------------------+
| Boolean(false) OR Boolean(true) |
+---------------------------------+
| true |
+---------------------------------+
Побитовые операторы¶
&(побитовое И)|(побитовое ИЛИ)#(побитовое исключающее ИЛИ)>>(побитовый сдвиг вправо)<<(побитовый сдвиг влево)
Оператор &¶
Побитовое И:
> SELECT 5 & 3;
+---------------------+
| Int64(5) & Int64(3) |
+---------------------+
| 1 |
+---------------------+
Оператор |¶
Побитовое ИЛИ:
> SELECT 5 | 3;
+---------------------+
| Int64(5) | Int64(3) |
+---------------------+
| 7 |
+---------------------+
Оператор #¶
Побитовое исключающее ИЛИ:
> SELECT 5 # 3;
+---------------------+
| Int64(5) # Int64(3) |
+---------------------+
| 6 |
+---------------------+
Оператор >>¶
Побитовый сдвиг вправо:
> SELECT 5 >> 3;
+----------------------+
| Int64(5) >> Int64(3) |
+----------------------+
| 0 |
+----------------------+
Оператор <<¶
Побитовый сдвиг влево:
> SELECT 5 << 3;
+----------------------+
| Int64(5) << Int64(3) |
+----------------------+
| 40 |
+----------------------+
Прочие операторы¶
||(объединение строк)@>(массив содержит)<@(массив содержится в)
Оператор ||¶
Объединение строк:
> SELECT 'Hello, ' || 'Tarantool Column Store!';
+----------------------------------------------------+
| Utf8("Hello, ") || Utf8("Tarantool Column Store!") |
+----------------------------------------------------+
| Hello, Tarantool Column Store! |
+----------------------------------------------------+
Оператор @>¶
Массив содержит:
> SELECT make_array(1,2,3) @> make_array(1,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3)) @> make_array(Int64(1),Int64(3)) |
+-------------------------------------------------------------------------+
| true |
+-------------------------------------------------------------------------+
Оператор <@¶
Массив содержится в:
> SELECT make_array(1,3) <@ make_array(1,2,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(3)) <@ make_array(Int64(1),Int64(2),Int64(3)) |
+-------------------------------------------------------------------------+
| true |
+-------------------------------------------------------------------------+
Агрегатные функции¶
Общие функции¶
array_agg
avg
bit_and
bit_or
bit_xor
bool_and
bool_or
count
first_value
grouping
last_value
max
mean
median
min
string add
sum
var
var_pop
var_population
var_samp
var_sample
Статистические функции¶
corr
covar
covar_pop
covar_samp
nth_value
regr_avgx
regr_avgy
regr_count
regr_intercept
regr_r2
regr_slope
regr_sxx
regr_syy
regr_sxy
stddev
stddev_pop
stddev_samp
Функции апроксимации¶
approx_distinct
approx_median
approx_percentile_cont
approx_percentile_cont_with_weight
Функция approx_distinct()¶
Возвращает приблизительное количество различных входных значений, рассчитанное с использованием алгоритма HyperLogLog.
approx_distinct(expression)
Аргументы:
expression: выражение для выполнения операции. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.
approx_median()¶
Возвращает приблизительную медиану (50-й процентиль) входных значений.
approx_median(expression)
Аргументы:
expression: выражение для выполнения операции. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.
approx_percentile_cont()¶
Возвращает приблизительный процентиль входных значений с использованием алгоритма t-digest.
approx_percentile_cont(expression, percentile, centroids)
Аргументы:
expression: выражение для работы. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.percentile: процентиль для выполнения операции. Должно быть значение с плавающей запятой от 0 до 1 (включительно).centroids: количество центроидов, которые будут использоваться в алгоритме t-digest. По умолчанию — 100.
Если существует такое количество или меньше уникальных значений, вы можете ожидать точного результата. Большее количество центроидов приводит к более точному приближению, но требует больше памяти для вычислений.
SELECT
items, approx_percentile_cont( price, 0.99 ) AS "99th_percentile"
FROM products
GROUP BY item;
approx_percentile_cont_with_weight()¶
Возвращает взвешенный приблизительный процентиль входных значений с использованием алгоритма t-digest.
approx_percentile_cont_with_weight(expression, weight, percentile)
Аргументы:
expression: выражение для работы. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.weight: выражение для использования в качестве веса. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.percentile: процентиль для вычисления. Должно быть значение с плавающей запятой от 0 до 1 (включительно).
SELECT
item, approx_percentile_cont_with_weight( price, 0.99 ) AS "co_weighted_99th_percentile"
FROM products
GROUP BY item;
Функции проверки вхождения ip-адреса в подсеть¶
is_in_net()¶
Проверка на принадлежность ip-адресов к сети net.
is_in_net(net: string, column: uint32) → bool
Аргументы:
netзадается в виде ip-адреса с маскойa.b.c.d/y
Возвращаемое значение:
true/false в зависимости от принадлежности
Пример:
SELECT * FROM attributes WHERE is_in_net(’192.162.5.0/24’, “AttributeX”) = true;
Оконные функции¶
Оконная функция выполняет вычисление над набором строк таблицы, связанных с текущей строкой. В отличие от агрегатных функций, они не сворачивают строки в одну, а сохраняют исходное количество записей, добавляя результаты вычислений для каждой строки. На самом деле, оконная функция имеет доступ не только к текущей строке результата запроса.
Пример: сравнение зарплаты каждого сотрудника со средней зарплатой в его отделе:
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
Результат:
+-----------+-------+--------+-------------------+
| depname | empno | salary | avg |
+-----------+-------+--------+-------------------+
| personnel | 2 | 3900 | 3700.0 |
| personnel | 5 | 3500 | 3700.0 |
| develop | 8 | 6000 | 5020.0 |
| develop | 10 | 5200 | 5020.0 |
| develop | 11 | 5200 | 5020.0 |
| develop | 9 | 4500 | 5020.0 |
| develop | 7 | 4200 | 5020.0 |
| sales | 1 | 5000 | 4866.666666666667 |
| sales | 4 | 4800 | 4866.666666666667 |
| sales | 3 | 4800 | 4866.666666666667 |
+-----------+-------+--------+-------------------+
Ключевой особенностью оконных функций является обязательное наличие конструкции OVER, которое следует сразу после имени функции и её аргументов. Именно эта конструкция определяет логику работы оконной функции. Внутри OVER с помощью конструкции PARTITION BY задаётся разделение данных на группы (партиции) по одинаковым значениям указанных столбцов или выражений. Для каждой строки вычисления производятся только в рамках её партиции.
Порядок обработки строк внутри партиции можно задать с помощью оператора ORDER BY в конструкции OVER. Важно отметить, что этот порядок может не совпадать с порядком вывода строк в финальном результате.
Пример:
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
Результат:
+-----------+-------+--------+--------+
| depname | empno | salary | rank |
+-----------+-------+--------+--------+
| personnel | 2 | 3900 | 1 |
| develop | 8 | 6000 | 1 |
| develop | 10 | 5200 | 2 |
| develop | 11 | 5200 | 2 |
| develop | 9 | 4500 | 4 |
| develop | 7 | 4200 | 5 |
| sales | 1 | 5000 | 1 |
| sales | 4 | 4800 | 2 |
| personnel | 5 | 3500 | 2 |
| sales | 3 | 4800 | 2 |
+-----------+-------+--------+--------+
Отдельное внимание стоит уделить концепции фрейма окна - набора строк внутри партиции, относительно текущей строки, над которыми производятся вычисления. Некоторые оконные функции работают исключительно в пределах фрейма, а не всей партиции.
Пример использования фрейма в запросах:
SELECT depname, empno, salary,
avg(salary) OVER(ORDER BY salary ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg,
min(salary) OVER(ORDER BY empno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_min
FROM empsalary
ORDER BY empno ASC;
Результат:
+-----------+-------+--------+--------------------+---------+
| depname | empno | salary | avg | cum_min |
+-----------+-------+--------+--------------------+---------+
| sales | 1 | 5000 | 5000.0 | 5000 |
| personnel | 2 | 3900 | 3866.6666666666665 | 3900 |
| sales | 3 | 4800 | 4700.0 | 3900 |
| sales | 4 | 4800 | 4866.666666666667 | 3900 |
| personnel | 5 | 3500 | 3700.0 | 3500 |
| develop | 7 | 4200 | 4200.0 | 3500 |
| develop | 8 | 6000 | 5600.0 | 3500 |
| develop | 9 | 4500 | 4500.0 | 3500 |
| develop | 10 | 5200 | 5133.333333333333 | 3500 |
| develop | 11 | 5200 | 5466.666666666667 | 3500 |
+-----------+-------+--------+--------------------+---------+
При использовании нескольких оконных функций в одном запросе можно избежать дублирования кода за счёт именованных окон. Для этого в конструкции WINDOW определяется шаблон окна, который затем многократно используется в конструкциях OVER различных функций.
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
Синтаксис оконной функции¶
Синтаксис конструкции OVER приведён в примере:
function([expr])
OVER(
[PARTITION BY expr[, …]]
[ORDER BY expr [ ASC | DESC ][, …]]
[ frame_clause ]
)
Где frame_clause может быть представлен одним из следующих выражений:
{ RANGE | ROWS | GROUPS } frame_start
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end
Возможные значения для frame_start и frame_end:
UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING
Где offset – неотрицательное целое число.
Примечание
Особые требования:
Для режимов RANGE и GROUPS обязательно наличие ORDER BY;
В режиме RANGE в ORDER BY должен быть указан ровно один столбец.
Агрегатные функции в оконной функции¶
Все агрегатные функции могут быть использованы в оконной функции.
Функции ранжирования¶
cume_dist
dense_rank
lag
last_value
lead
nth_value
ntile
percent_rank
rank
row_number
cume_dist¶
Вычисляет относительный ранг текущей строки в виде дроби от 0 до 1, показывающей, какая часть строк имеет значения меньшие или равные текущему.
cume_dist()
Пример:
SELECT salary,
cume_dist() OVER (ORDER BY salary) AS cume_dist
FROM employees;
Результат:
+--------+-----------+
| salary | cume_dist |
+--------+-----------+
| 30000 | 0.33 | -- 1/3 строк ≤ 30000
| 50000 | 0.67 | -- 2/3 строк ≤ 50000
| 70000 | 1.00 | -- 3/3 строк ≤ 70000
+--------+-----------+
dense_rank¶
Возвращает ранг текущей строки без пропусков. Присваивает последовательные ранги, сохраняя одинаковые ранги для строк с одинаковыми значениями.
dense_rank()
Пример:
SELECT department,
salary,
dense_rank() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
Результат:
+-------------+--------+------------+
| department | salary | dense_rank |
+-------------+--------+------------+
| Sales | 70000 | 1 |
| Sales | 50000 | 2 |
| Sales | 50000 | 2 | -- одинаковые значения = одинаковый ранг
| Sales | 30000 | 3 | -- следующий ранг без пропуска (не 4)
| Engineering | 90000 | 1 |
| Engineering | 80000 | 2 |
+-------------+--------+------------+
lag¶
Возвращает значение из строки, находящейся на указанном смещении (offset) перед текущей строкой в рамках партиции. Если такой строки не существует, возвращает значение по умолчанию. Аналогична функции lead(), но смотрит назад, а не вперед.
lag(expression, offset, default)
Аргументы:
expression- выражение, столбец или выражение для вычисления;offset- смещение, количество строк назад (целое число, по умолчанию 1);default- значение по умолчанию, возвращаемое значение при выходе за границы партиции (должно совпадать по типу с выражением).
Пример:
SELECT employee_id,
salary,
lag(salary, 1, 0) OVER (ORDER BY employee_id) AS prev_salary
FROM employees;
Результат:
+-------------+--------+-------------+
| employee_id | salary | prev_salary |
+-------------+--------+-------------+
| 1 | 30000 | 0 | -- нет предыдущей строки - возвращает 0
| 2 | 50000 | 30000 | -- значение из предыдущей строки
| 3 | 70000 | 50000 |
| 4 | 60000 | 70000 |
+-------------+--------+-------------+
last_value¶
Возвращает значение выражения из последней строки текущего окна (фрейма).
last_value(expression)
Аргументы:
expression- выражение, столбец или вычисляемое выражение.
Пример:
SELECT department,
employee_id,
salary,
last_value(salary) OVER (
PARTITION BY department
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS dept_max_salary
FROM employees;
Результат:
+-------------+-------------+--------+-----------------+
| department | employee_id | salary | dept_max_salary |
+-------------+-------------+--------+-----------------+
| Sales | 1 | 30000 | 70000 |
| Sales | 2 | 50000 | 70000 |
| Sales | 3 | 70000 | 70000 |
| Engineering | 4 | 40000 | 60000 |
| Engineering | 5 | 60000 | 60000 |
+-------------+-------------+--------+-----------------+
lead¶
Возвращает значение из строки, находящейся на указанном смещении (offset) после текущей строки в рамках партиции. Если такой строки не существует, возвращает значение по умолчанию. Аналогична функции lag(), но смотрит вперед, а не назад.
lead(expression, offset, default)
Аргументы:
expression- выражение, столбец или выражение для вычисления;offset- смещение, количество строк назад (целое число, по умолчанию 1);default- значение по умолчанию, возвращаемое значение при выходе за границы партиции (должно совпадать по типу с выражением).
Пример:
SELECT
employee_id,
department,
salary,
lead(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;
Результат:
+-------------+-------------+--------+-------------+
| employee_id | department | salary | next_salary |
+-------------+-------------+--------+-------------+
| 1 | Sales | 30000 | 50000 | -- следующая зарплата в отделе
| 2 | Sales | 50000 | 70000 |
| 3 | Sales | 70000 | 0 | -- нет следующей строки (возвращает 0)
| 4 | Engineering | 40000 | 60000 |
| 5 | Engineering | 60000 | 0 |
+-------------+-------------+--------+-------------+
nth_value¶
Возвращает значение выражения из n-й строки текущего окна (фрейма). Если строка с указанным номером отсутствует, возвращает NULL.
nth_value(expression, n)
Аргументы:
expression- выражение, столбец или вычисляемое выражение;n- номер строки в окне (начиная с 1).
Пример:
SELECT
id,
salary,
nth_value(salary, 2) OVER (
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_salary
FROM employees;
Результат:
+----+--------+---------------+
| id | salary | second_salary |
+----+--------+---------------+
| 1 | 30000 | 40000 |
| 2 | 40000 | 40000 |
| 3 | 50000 | 40000 |
| 4 | 60000 | 40000 |
| 5 | 70000 | 40000 |
+----+--------+---------------+
ntile¶
Разбивает строки партиции на указанное количество групп с приблизительно равным числом элементов.
ntile(expression)
Аргументы:
expression- количество групп, целое число, на сколько групп разделить данные.
Пример:
SELECT
employee_id,
salary,
ntile(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
Результат:
+-------------+--------+----------+
| employee_id | salary | quartile |
+-------------+--------+----------+
| 1 | 90000 | 1 |
| 2 | 85000 | 1 |
| 3 | 80000 | 2 |
| 4 | 70000 | 2 |
| 5 | 60000 | 3 |
| 6 | 50000 | 3 |
| 7 | 40000 | 4 |
| 8 | 30000 | 4 |
+-------------+--------+----------+
percent_rank¶
Вычисляет относительный ранг текущей строки в пределах партиции в виде значения от 0 до 1 по формуле:
(ранг_строки - 1) / (общее_количество_строк - 1)
percent_rank()
Пример:
SELECT
employee_id,
salary,
percent_rank() OVER (ORDER BY salary) AS percentile
FROM employees;
Результат:
+-------------+--------+------------+
| employee_id | salary | percentile |
+-------------+--------+------------+
| 1 | 30000 | 0.00 | -- минимальное значение (0%)
| 2 | 50000 | 0.50 | -- среднее значение (50%)
| 3 | 70000 | 1.00 | -- максимальное значение (100%)
+-------------+--------+------------+
rank¶
Присваивает ранг каждой строке в пределах партиции, пропуская номера для строк с одинаковыми значениями, оставляя пропуски в нумерации. Функция работает аналогично row_number(), но пропускает номера рангов для одинаковых значений.
rank()
Пример:
SELECT
department,
salary,
rank() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
Результат:
+-------------+--------+-----------+
| department | salary | dept_rank |
+-------------+--------+-----------+
| Sales | 70000 | 1 |
| Sales | 50000 | 2 | -- две строки с одинаковым значением
| Sales | 50000 | 2 | -- получают одинаковый ранг
| Sales | 30000 | 4 | -- следующий ранг пропускает номер 3
| Engineering | 90000 | 1 |
| Engineering | 80000 | 2 |
+-------------+--------+-----------+
row_number¶
Присваивает последовательный номер каждой строке в пределах партиции, начиная с 1.
row_number()
Пример:
SELECT department,
salary,
row_number() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
Результат:
+-------------+--------+---------+
| department | salary | row_num |
+-------------+--------+---------+
| Sales | 70000 | 1 |
| Sales | 50000 | 2 |
| Sales | 50000 | 3 | -- одинаковые значения, но разные номера
| Sales | 30000 | 4 |
| Engineering | 90000 | 1 |
| Engineering | 80000 | 2 |
+-------------+--------+---------+
Отличия от других функций ранжирования:
в отличие от rank() не пропускает номера при одинаковых значениях;
в отличие от dense_rank() всегда присваивает последовательные номера без повторов.
Скалярные функции¶
Математические функции¶
abs
acos
acosh
asin
asinh
atan
atanh
atan2
cbrt
ceil
cos
cosh
degrees
exp
factorial
floor
gcd
isnan
iszero
lcm
ln
log
log10
log2
nanvl
pi
power
pow
radians
random
round
signum
sin
sinh
sqrt
tan
tanh
trunc
abs¶
Возвращает абсолютное значение (модуль) целого числа.
abs(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
acos¶
Возвращает арккосинус или обратный косинус числа.
acos(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
acosh¶
Возвращает гиперболический косинус площади или обратный гиперболический косинус числа.
acosh(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
asin¶
Возвращает арксинус или обратный синус числа.
asin(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
asinh¶
Возвращает гиперболический синус площади или обратный гиперболический синус числа.
asinh(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
atan¶
Возвращает арктангенс или обратный тангенс числа.
atan(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
atanh¶
Возвращает гиперболический тангенс площади или обратный гиперболический тангенс числа.
atanh(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
atan2¶
Возвращает арктангенс или обратный тангенс частного expression_y / expression_x.
atan2(expression_y, expression_x)
Аргументы:
expression_x- первое числовое выражение (числитель дроби). Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.expression_y- второе числовое выражение (знаменатель дроби). Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
cbrt¶
Возвращает кубический корень числа.
cbrt(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
ceil¶
Возвращает ближайшее целое число, большее чем или равное заданному числу.
ceil(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
cos¶
Возвращает косинус числа.
cos(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
cosh¶
Возвращает гиперболический косинус числа.
cosh(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
degrees¶
Переводит меру числа из радиан в градусы.
degrees(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
exp¶
Возвращает экспоненту числа по основанию e.
exp(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
factorial¶
Факториал. Возвращает 1, если значение меньше 2.
factorial(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
floor¶
Возвращает ближайшее целое число, меньшее чем или равное заданному числу.
floor(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
gcd¶
Возвращает наибольший больший делитель частного expression_x / expression_y.
Возвращает 0, если оба введенных аргумента равны NULL.
gcd(expression_x, expression_y)
Аргументы:
expression_x- первое числовое выражение (числитель дроби). Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.expression_y- второе числовое выражение (знаменатель дроби). Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
isnan¶
Возвращает true (правда), если введенное число является +NaN или -NaN, иначе возвращает ложь (false).
isnan(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
iszero¶
Возвращает true (правда), если введенное число равно +0.0 или -0.0, иначе возвращает ложь (false).
iszero(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
lcm¶
Возвращает наименьший общий множитель частного expression_x / expression_y.
Возвращает 0, если какой-либо из введенных аргументов равен NULL.
lcm(expression_x, expression_y)
Аргументы:
expression_x- первое числовое выражение (числитель дроби). Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.expression_y- второе числовое выражение (знаменатель дроби). Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
ln¶
Возвращает натуральный логарифм числа.
ln(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
log¶
Возвращает логарифм числа по заданному основанию. В качестве основания берется либо явно заданное число, либо 10 в том случае, когда основание не задано.
log(base, numeric_expression)
log(numeric_expression)
Аргументы:
base- основание логарифма. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
log10¶
Возвращает десятичный логарифм числа.
log10(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
log2¶
Возвращает двоичный логарифм числа.
log2(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
nanvl¶
Возвращает первый аргумент, если он не является NaN. В противном случае возвращает второй аргумент.
nanvl(expression_x, expression_y)
Аргументы:
expression_x- первое числовое выражение (числитель дроби). Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.expression_y- второе числовое выражение (знаменатель дроби). Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
power¶
Возвращает число, возведенное в заданную степень.
power(base, exponent)
Аргументы:
base- основание для возведения в степень. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.exponent- показатель степени. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
Алиасы: pow
pow¶
Алиас функции power.
radians¶
Переводит меру числа из градусов в радианы.
radians(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
random¶
Возвращает случайное значение с плавающей точкой в диапазоне [0, 1).
Случайное начальное значение (seed) уникально для каждой строки.
random()
round¶
Округляет число к ближайшему целому числу.
round(numeric_expression[, decimal_places])
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.decimal_places- необязательный аргумент. Означает количество десятичных разрядов в целевом числе, к которому производится округление. По умолчанию берется равным 0.
signum¶
Возвращает знак числа. Для отрицательных чисел возвращает -1. Для нуля и положительных чисел возвращает 1.
signum(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
sin¶
Возвращает синус числа.
sin(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
sinh¶
Возвращает гиперболический синус числа.
sinh(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
sqrt¶
Возвращает квадратный корень числа.
sqrt(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
tan¶
Возвращает тангенс числа.
tan(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
tanh¶
Возвращает гиперболический тангенс числа.
tanh(numeric_expression)
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
trunc¶
Усекает число до целого числа или до указанных десятичных разрядов.
trunc(numeric_expression[, decimal_places])
Аргументы:
numeric_expression- числовое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.decimal_places- необязательный аргумент. Количество десятичных разрядов для усечения. По умолчанию берется 0 (усечение до целого числа). Если указано положительное целое число, усекает цифры справа от десятичной точки. Если указано отрицательное число, то заменяет цифры слева от десятичной точки на 0.
Условные функции¶
coalesce
nullif
nvl
nvl2
ifnull
coalesce¶
Возвращает первый из аргументов, который не равен NULL.
Возвращает NULL, если все аргументы равны NULL.
Эту функцию часто используют вместо значения по умолчанию для нулевых значений.
coalesce(expression1[, ..., expression_n])
Аргументы:
expression_1,expression_n- выражение, которое используется, если предыдущие выражения равны NULL. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов. Можно передавать столько аргументов, сколько требуется.
nullif¶
Возвращает NULL, если expression1 равен expression2. В противном случае
возвращает expression1. Может использоваться для выполнения задачи, обратной
coalesce.
nullif(expression1, expression2)
Аргументы:
expression1- числовое выражение, которое нужно сравнить сexpression2и вернуть, если они равны. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.expression2- числовое выражение, которое нужно сравнить сexpression1. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
nvl¶
Возвращает expression2, если expression1 равно NULL. В противном случае
возвращает expression1.
nvl(expression1, expression2)
Аргументы:
expression1- числовое выражение, которое нужно вернуть, если оно не равно NULL. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.expression2- числовое выражение, которое нужно вернуть, еслиexpression1равно NULL. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
nvl2¶
Возвращает expression2, если expression1 не равно NULL. В противном случае
возвращает expression3.
nvl2(expression1, expression2, expression3)
Аргументы:
expression1- условное выражение. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.expression2- числовое выражение, которое нужно вернуть, еслиexpression1не равно NULL. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.expression3- числовое выражение, которое нужно вернуть, еслиexpression1равно NULL. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов.
ifnull¶
Алиас для функции nvl.
Строковые функции¶
ascii
bit_length
btrim
char_length
character_length
concat
concat_ws
chr
ends_with
initcap
left
length
lower
lpad
ltrim
octet_length
repeat
replace
reverse
right
rpad
rtrim
split_part
starts_with
strpos
substr
to_hex
translate
trim
upper
uuid
overlay
levenshtein
substr_index
find_in_set
position
ascii()¶
Возвращает ASCII-код первого символа в строке.
ascii(str)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
Обратная функция: chr
SELECT ascii('Привет, Тарантул')
>> 1055
bit_length()¶
Возвращает длину строки в битах.
bit_length(str)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
Похожие функции: length, octet_length
SELECT bit_length('four')
>> 32
btrim()¶
Отсекает указанную подстроку в начале и в конце обрабатываемой строки. Если подстрока не указана, то убирает все пробелы в начале и в конце обрабатываемой строки.
btrim(str[, trim_str])
Аргументы:
str- обрабатываемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.trim_str- строковое выражение, которое следует отсечь в начале и в конце обрабатываемой строки. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов. По умолчанию – пробелы.
Похожие функции: ltrim, rtrim
Алиасы: trim
SELECT btrim('to trim to trim Hello, Tarantool Column Store!to trim ', 'to trim ')
>> 'Hello, Tarantool Column Store!'
chr()¶
Возвращает символ, которому соответствует указанный ASCII- или Unicode-код.
chr(expression)
Аргументы:
expression- выражение, содержащее ASCII- или Unicode-код. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических или строковых операторов.
Обратная функция: ascii
SELECT chr (1055)
>> 'П'
concat()¶
Объединяет множество строк в одну.
concat(str[, ..., str_n])
Аргументы:
str- первое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.str_n- последующее строковое выражение или строковый столбец.
Похожие функции: concat_ws
SELECT concat('Hello,','Tarantool Column','Store!')
>> 'Hello,Tarantool ColumnStore!'
concat_ws()¶
Объединяет множество строк в одну, вставляя между ними указанный разделитель.
concat(separator, str[, ..., str_n])
Аргументы:
separator- разделитель для вставки между строк.str- первое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.str_n- последующее строковое выражение или строковый столбец.
Похожие функции: concat
SELECT concat_ws(' ','Hello,','Tarantool Column','Store!')
>> 'Hello, Tarantool Column Store!'
initcap()¶
Делает заглавной первую букву в каждом слове обрабатываемой строки. Разделителями между словами считаются все не буквенно-цифровые символы.
initcap(str)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
Похожие функции: lower, upper
SELECT initcap('hello, tarantool column store!')
>> 'Hello, Tarantool Column Store!'
left()¶
Возвращает указанное количество символов от левого края строки.
left(str, n)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.n- количество символов, которое надо вернуть.
Похожие функции: right
SELECT left('Hello, Tarantool Column Store!', 5)
>> 'Hello'
length()¶
Возвращает количество символов в строке.
length(str)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
Похожие функции: bit_length, octet_length
Алиасы: char_length, character_length
SELECT length('four')
>> 4
lower()¶
Превращает все буквы обрабатываемой строки в строчные.
lower(str)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
Похожие функции: initcap, upper
SELECT lower('Hello, Tarantool Column Store!')
>> 'hello, tarantool column store!'
ltrim()¶
Отсекает указанную подстроку в начале обрабатываемой строки. Если подстрока не указана, то убирает все пробелы в начале обрабатываемой строки.
ltrim(str[, trim_str])
Аргументы:
str- обрабатываемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.trim_str- строковое выражение, которое следует отсечь в начале обрабатываемой строки. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов. По умолчанию – пробелы.
Похожие функции: btrim, rtrim
SELECT ltrim('to trim to trim Hello, Tarantool Column Store!to trim ', 'to trim ')
>> 'Hello, Tarantool Column Store!to trim '
octet_length()¶
Возвращает длину строки в байтах.
octet_length(str)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
Похожие функции: bit_length, length
SELECT octet_length('four')
>> 4
repeat()¶
Возвращает строку, состоящую из указанного количества повторов заданной строки.
repeat(str, n)
Аргументы:
str- строковое выражение, которое следует повторить. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.n- количество повторов.
SELECT repeat('Hello, Tarantool Column Store! ', 3)
>> 'Hello, Tarantool Column Store! Hello, Tarantool Column Store! Hello, Tarantool Column Store! '
replace()¶
Заменяет все вхождения указанной подстроки в строке на новую подстроку.
replace(str, substr, replacement)
Аргументы:
str- обрабатываемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.substr- подстрока, которую следует найти и заменить в строкеstr. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.replacement- подстрока, на которую следует заменить найденные вхожденияsubstrв строкеstr. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
SELECT replace('Hello, Tarantool Column Store!', ' ', ' Super-')
>> 'Hello, Super-Tarantool Super-Column Super-Store!'
reverse()¶
Переставляет символы в строке в противоположном порядке (зеркальное отображение).
reverse(str)
Аргументы:
str- строковое выражение, окторое нужно отобразить зеркально. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
SELECT reverse('Hello, Tarantool Column Store!')
>> '!erotS nmuloC lootnaraT ,olleH'
right()¶
Возвращает указанное количество символов от правого края строки.
right(str, n)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.n- количество символов, которое надо вернуть.
Похожие функции: left
SELECT right('Hello, Tarantool Column Store!', 6)
>> 'Store!'
rtrim()¶
Отсекает указанную подстроку в конце обрабатываемой строки. Если подстрока не указана, то убирает все пробелы в конце обрабатываемой строки.
rtrim(str[, trim_str])
Аргументы:
str- обрабатываемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.trim_str- строковое выражение, которое следует отсечь в конце обрабатываемой строки. Можно задать константу, столбец, функцию, а также любую комбинацию арифметических операторов. По умолчанию – пробелы.
Похожие функции: btrim, ltrim
SELECT rtrim('to trim to trim Hello, Tarantool Column Store!to trim ', 'to trim ')
>> 'to trim to trim Hello, Tarantool Column Store!'
split_part()¶
Разбивает строку на подстроки по указанному разделителю и возвращает подстроку с указанным номером.
split_part(str, delimiter, pos)
Аргументы:
str- обрабатываемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.delimiter- строка или символ разделителя.pos- номер возвращаемой подстроки.
SELECT split_part('Hello, Tarantool Column Store!', ' ', 3)
>> 'Column'
starts_with()¶
Проверяет, начинается ли строка с указанной подстроки.
starts_with(str, substr)
Аргументы:
str- проверяемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.substr- подстрока, вхождение которой нужно проверить.
SELECT starts_with('Hello, Tarantool Column Store!', 'Hello')
>> true
strpos()¶
Возвращает начальную позицию указанной подстроки в строке. Отсчет позиции начинается с 1. Если строка не содержит указанную подстроку, то функция возвращает 0.
strpos(str, substr)
Аргументы:
str- обрабатываемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.substr- подстрока, которую следует найти в строкеstr. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
Алиасы: instr
SELECT strpos('Hello, Tarantool Column Store!', 'tool')
>> 13
substr()¶
Вырезает подстроку указанной длины (в символах), начиная с указанной позиции в строке.
substr(str, start_pos[, length])
Аргументы:
str- обрабатываемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.start_pos- позиция (в символах от начала строки), начиная с которой нужно вырезать подстроку. Нумерация символов начинается с 1.length- количество символов в вырезаемой подстроке. Если не указано, то функция вернет всю строку, начиная с указанной стартовой позиции.
SELECT substr('Hello, Tarantool Column Store!', 8, 9)
>> 'Tarantool'
translate()¶
Меняет символы в строке на указанные символы.
translate(str, chars, translation)
Аргументы:
str- обрабатываемое строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.chars- символы, которые нужно заменить символамиtranslation.translation- символы, на которые нужно заменить символыchars. Замена производится в том порядке, в котором идут символы в двух последних аргументах.
SELECT translate('Hello, Tarantool Column Store!', 'tol', '+0|')
>> 'He||0, Taran+00| C0|umn S+0re!'
upper()¶
Превращает все буквы обрабатываемой строки в заглавные.
upper(str)
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.
Похожие функции: initcap, lower
SELECT upper('Hello, Tarantool Column Store!')
>> 'HELLO, TARANTOOL COLUMN STORE!'
Двоичные строковые функции¶
decode
encode
Функции для работы с регулярными выражениями¶
TCS поддерживает синтаксис регулярных выражений в стиле PCRE за исключением некоторых возможностей (например, нет поддержки просмотра (look-around) и обратных ссылок).
regexp_match
regexp_replace
regexp_match()¶
Возвращает список вхождений в указанную строку, которые удовлетворяют заданному регулярному выражению.
regexp_match(str, regexp[, flags])
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.regexp- регулярное выражение, для которого следует найти соответствия в указанной строке. Можно задать константу, колонку или функцию.flags- необязательный аргумент. Флаги, которые влияют на логику регулярного выражения. Можно указать следующие флаги:i- искать без учета регистраm- многострочный режим (^и$соответствуют началу и концу строки)s- символ.может соответствовать символу\nR- режим CRLF (когда включен многострочный режим, используется \r\n)U- поменять местами значенияx*иx*?
SELECT regexp_match('aBc', '(b|d)', 'i')
>> 'B'
SELECT regexp_match('I have been in Köln in 2018', '[a-zA-Z]ö[a-zA-Z]{2}')
>> 'Köln'
regexp_replace()¶
Заменяет вхождения указанной подстроки, которые удовлетворяют заданному регулярному выражению.
regexp_replace(str, regexp, replacement[, flags])
Аргументы:
str- строковое выражение. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.regexp- регулярное выражение, для которого следует найти соответствия в указанной строке. Можно задать константу, колонку или функцию.replacement- подстрока, на которую следует заменить найденные вхождения в строкеstr. Можно задать константу, столбец, функцию, а также любую комбинацию строковых операторов.flags- необязательный аргумент. Флаги, которые влияют на логику регулярного выражения. Можно указать следующие флаги:g- глобальный поиск (не прекращать поиск после первого найденного вхождения)i- искать без учета регистраm- многострочный режим (^и$соответствуют началу и концу строки)s- символ.может соответствовать символу\nR- режим CRLF (когда включен многострочный режим, используется \r\n)U- поменять местами значенияx*иx*?
SELECT regexp_replace('aBc', '(b|d)', 'Ab\\1a', 'i')
>> 'aAbBac'
SELECT regexp_replace('foobarbaz', 'b(..)', 'X\\1Y', 'g')
>> 'fooXarYXazY',
Функции для работы со временем и датами¶
current_date¶
Возвращает текущую дату в формате UTC.
Значение, возвращаемое функцией current_date(), определяется во время выполнения запроса
и будет одинаковым независимо от того, в какой части плана выполнения запроса вызывается
эта функция.
current_date()
Аналоги: today
current_time¶
Возвращает текущее время в формате UTC.
Значение, возвращаемое функцией current_time(), определяется во время выполнения запроса
и будет одинаковым независимо от того, в какой части плана выполнения запроса вызывается
эта функция.
current_time()
date_bin¶
Вычисляет времянные интервалы и возвращает начало ближайшего интервала к указанной метке времени.
Функция date_bin используется для понижающей дискретизации времянных рядов путём
группировки строк в «корзины» (bins, бины) на основе времени и применения агрегатной
или селекторной функции к каждому интервалу.
Например, если данные группируются в 15-минутные интервалы, метка времени
2023-01-01T18:18:18Z будет приведена к началу соответствующего 15-минутного интервала:
2023-01-01T18:15:00Z.
Синтаксис:
date_bin(interval, expression, [origin-timestamp])
Аргументы:
interval– интервал, шаг бина (например, 15 minutes, 1 day);expression– выражение, времянное выражение (константа, столбец или функция);[необязательный]
origin-timestamp- начальная метка времени. Tочка отсчёта для определения границ бинов. По умолчанию:1970-01-01T00:00:00Z(UNIX-эпоха в UTC).
Поддерживаемые интервалы:
nanoseconds- наносекунды;microseconds- микросекунды;milliseconds- миллисекунды;seconds- секунды;minutes- минуты;hours- часы;days- дни;weeks- недели;months- месяцы;years- годы;century- век;
Функция полезна для анализа времянных данных с группировкой по произвольным интервалам.
Пример: группировка меток времени в интервалы по 1 дню:
SELECT date_bin(interval '1 day', time) as bin
FROM VALUES ('2023-01-01T18:18:18Z'), ('2023-01-03T19:00:03Z') t(time);
Результат:
+---------------------+
| bin |
+---------------------+
| 2023-01-01T00:00:00 |
| 2023-01-03T00:00:00 |
+---------------------+
Пример: группировка с началом отсчёта в 3:00 (вместо 00:00):
SELECT date_bin(interval '1 day', time, '2023-01-01T03:00:00') as bin
FROM VALUES ('2023-01-01T18:18:18Z'), ('2023-01-03T19:00:03Z') t(time);
Результат:
+---------------------+
| bin |
+---------------------+
| 2023-01-01T03:00:00 |
| 2023-01-03T03:00:00 |
+---------------------+
date_part¶
Извлекает указанную часть даты/времени и возвращает её как целое число.
date_part(part, expression)
Аргументы:
part- часть даты. Поддерживаемые части даты:year- годquarter- квартал (1-4)month- месяц (1-12)week- неделя года (1-53)day- день месяца (1-31)hour- час (0-23)minute- минута (0-59)second- секунда (0-59)millisecond- миллисекундыmicrosecond- микросекундыnanosecond- наносекундыdow- день недели (0-6, где 0 - воскресенье)doy- день года (1-366)epoch- секунды с Unix эпохи (1970-01-01 00:00:00 UTC)
expression- времянное выражение, может быть константой, колонкой, или функцией
Альтернативный синтаксис:
extract(field FROM source)
Аналоги:
date_trunc¶
Обрезает метку времени до указанной точности, обнуляя младшие части.
date_trunc(precision, expression)
Аргументы:
precision- точность округления. Допустимые значения:year/YEAR- обрезает до года (01-01)quarter/QUARTER- до начала кварталаmonth/MONTH- до начала месяцаweek/WEEK- до понедельника неделиday/DAY- до начала суток (00:00:00)hour/HOUR- до начала часаminute/MINUTE- до начала минутыsecond/SECOND- до начала секунды
expression- времянное выражение; может быть константой, колонкой, или функцией
Аналоги:
datetrunc¶
Аналог функции date_trunc.
from_unixtime¶
Преобразует Unix-время (количество секунд с 1970-01-01) во метку времени (timestamp) формата RFC3339.
from_unixtime(expression[, timezone])
Аргументы:
expression- выражение для обработки. Может быть константой, столбцом или функцией, а также любой комбинацией операторов.timezone- часовой пояс для преобразования целого числа в метку времени. Если не указан, по умолчанию используется UTC.
Пример:
select from_unixtime(1599572549, 'America/New_York');
Результат:
+-----------------------------------------------------------+
| 2020-09-08T09:42:29-04:00 |
+-----------------------------------------------------------+
make_date¶
Создает дату из отдельных компонентов года, месяца и дня.
make_date(year, month, day)
Аргументы:
year- год для создания даты. Может быть константой, столбцом или функцией, включая арифметические операции.month- месяц для создания даты (1-12). Может быть константой, столбцом или функцией, включая арифметические операции.day- день для создания даты (1-31). Может быть константой, столбцом или функцией, включая арифметические операции.
Пример:
SELECT make_date(2023, 1, 31);
Результат:
+-----------------+
| 2023-01-31 |
+-----------------+
Другой пример:
SELECT make_date('2023', '01', '31');
Результат:
+-----------------+
| 2023-01-31 |
+-----------------+
now¶
Возвращает текущую метку времени в формате UTC.
Значение, возвращаемое функцией now(), определяется во время выполнения запроса
и будет одинаковым независимо от того, в какой части плана выполнения запроса
вызывается эта функция.
now
Аналоги: current_timestamp
to_char¶
Преобразует дату, время или метку времени в строковое представление согласно заданному формату. В отличие от PostgreSQL, не поддерживает числовое форматирование.
to_char(expression, format)
Аргументы:
expression- входные данные для преобразования (дата, время, метка времени или интервал). Может быть константой, столбцом или функцией, возвращающей дату, время, метку времени или длительность.format- cтрока формата Chrono для преобразования значения.
Пример:
SELECT to_char('2023-03-01'::date, '%d-%m-%Y');
Результат:
+----------------------------------------------+
| 01-03-2023 |
+----------------------------------------------+
Аналоги:
to_date¶
Преобразует значение в формат даты (YYYY-MM-DD). Принимает строки, целые и дробные числа в качестве входных данных. Строки обрабатываются как YYYY-MM-DD (например, „2023-07-20“), если не указаны форматы Chrono. Целые и дробные числа интерпретируются как количество дней, прошедших с Unix-эпохи (1970-01-01T00:00:00Z). Возвращает соответствующую дату.
Примечание
to_date() возвращает Date32, который представляет значения как количество дней
с Unix-эпохи (1970-01-01), хранящееся как 32-битное знаковое число.
Максимальная поддерживаемая дата - 9999-12-31.
to_date(expression[, ..., format_n])
Аргументы:
expression- строковое выражение для преобразования. Может быть константой, столбцом или функцией.format_n- опциональные строки формата Chrono для парсинга. Форматы проверяются по порядку, используется первый успешный вариант. Если ни один формат не подходит, возвращается ошибка.
Пример: простое преобразование строки:
SELECT to_date('2023-01-31');
Результат:
+-----------------+
| 2023-01-31 |
+-----------------+
Пример: преобразование с альтернативными форматами:
SELECT to_date('2023/01/31', '%Y-%m-%d', '%Y/%m/%d');
Результат:
+-----------------+
| 2023-01-31 |
+-----------------+
to_local_time¶
Преобразует метку времени с часовым поясом в локальное время (без информации о часовом поясе или смещении). Корректно обрабатывает переходы на летнее/зимнее время.
to_local_time(expression)
Аргументы:
expression- времянное выражение, может быть константой, столбцом или функцией
Пример: базовое преобразование:
SELECT to_local_time('2024-04-01T00:00:20Z'::timestamp);
Результат:
+---------------------+
| 2024-04-01T00:00:20 |
+---------------------+
Пример: преобразование с явным указанием часового пояса:
SELECT to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels');
Результат:
+---------------------+
| 2024-04-01T00:00:20 |
+---------------------+
Пример: проверка типов данных:
SELECT
time,
arrow_typeof(time) as type,
to_local_time(time) as to_local_time,
arrow_typeof(to_local_time(time)) as to_local_time_type
FROM (
SELECT '2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels' AS time
);
Результат:
+---------------------------+------------------------------------------------+---------------------+-----------------------------+
| time | type | to_local_time | to_local_time_type |
+---------------------------+------------------------------------------------+---------------------+-----------------------------+
| 2024-04-01T00:00:20+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-04-01T00:00:20 | Timestamp(Nanosecond, None) |
+---------------------------+------------------------------------------------+---------------------+-----------------------------+
Пример: комбинация с date_bin() для группировки по локальному времени:
SELECT date_bin(interval '1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AS date_bin;
Результат:
+---------------------+
| date_bin |
+---------------------+
| 2024-04-01T00:00:00 |
+---------------------+
Пример: группировка с восстановлением часового пояса:
SELECT date_bin(interval '1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AT TIME ZONE 'Europe/Brussels' AS date_bin_with_timezone;
Результат:
+---------------------------+
| date_bin_with_timezone |
+---------------------------+
| 2024-04-01T00:00:00+02:00 |
+---------------------------+
to_timestamp¶
Преобразует значение в метку времени (YYYY-MM-DDT00:00:00Z).
Поддерживает строки, целые числа (включая беззнаковые) и числа с плавающей точкой
в качестве входных данных. Строки обрабатываются как RFC3339 (например, „2023-07-20T05:44:00“),
если не указаны форматы Chrono. Целые числа (включая беззнаковые) и числа с плавающей точкой
интерпретируются как секунды с Unix-эпохи (1970-01-01T00:00:00Z).
Возвращает соответствующую метку времени.
Примечание
to_timestamp возвращает Timestamp(Nanosecond).
Поддерживаемый диапазон для целочисленных входных данных: от -9223372037 до 9223372036.
Поддерживаемый диапазон для строковых входных данных: от 1677-09-21T00:12:44.0 до 2262-04-11T23:47:16.0.
Для входных данных вне поддерживаемого диапазона используйте to_timestamp_seconds().
to_timestamp(expression[, ..., format_n])
Аргументы:
expression- выражение для обработки. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.format_n- опциональные строки формата Chrono для обработки выражения. Форматы проверяются в порядке их указания, и возвращается первый успешный результат. Если ни один из форматов не может обработать выражение, возвращается ошибка.
Пример: преобразование строки RFC3339:
SELECT to_timestamp('2023-01-31T09:26:56.123456789-05:00');
Результат:
+-----------------------------------------+
| 2023-01-31T14:26:56.123456789 |
+-----------------------------------------+
Пример преобразования с альтернативными форматами:
SELECT to_timestamp(
'03:59:00.123456789 05-17-2023',
'%c',
'%+',
'%H:%M:%S%.f %m-%d-%Y'
);
Результат:
+-----------------------------------------+
| 2023-05-17T03:59:00.123456789 |
+-----------------------------------------+
to_timestamp_micros¶
Преобразует значение в метку времени формата YYYY-MM-DDT00:00:00.000000Z.
Поддерживает строки, целые и беззнаковые целые числа в качестве входных данных.
Строки обрабатываются как RFC3339 (например, „2023-07-20T05:44:00“), если не указаны форматы Chrono.
Числа интерпретируются как микросекунды с Unix-эпохи (1970-01-01T00:00:00Z).
Возвращает соответствующую метку времени.
to_timestamp_micros(expression[, ..., format_n])
Аргументы:
expression- выражение для обработки. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.format_n- опциональные строки формата Chrono для обработки выражения. Форматы проверяются в порядке их указания, и возвращается первый успешный результат. Если ни один из форматов не может обработать выражение, возвращается ошибка.
Пример: преобразование строки RFC3339:
SELECT to_timestamp_micros('2023-01-31T09:26:56.123456789-05:00');
Результат:
+-----------------------------------------+
| 2023-01-31T14:26:56.123456 |
+-----------------------------------------+
Пример: преобразование с альтернативными форматами:
SELECT to_timestamp_micros(
'03:59:00.123456789 05-17-2023',
'%c',
'%+',
'%H:%M:%S%.f %m-%d-%Y'
);
Результат:
+-----------------------------------------+
| 2023-05-17T03:59:00.123456 |
+-----------------------------------------+
to_timestamp_millis¶
Преобразует значение в метку времени формата YYYY-MM-DDT00:00:00.000Z.
Поддерживает строковые и целочисленные типы данных на входе.
Строки обрабатываются как RFC3339 (например, „2023-07-20T05:44:00“), если не указаны форматы Chrono.
Числа интерпретируются как микросекунды с Unix-эпохи (1970-01-01T00:00:00Z).
Возвращает соответствующую метку времени.
to_timestamp_millis(expression[, ..., format_n])
Аргументы:
expression- выражение для обработки. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.format_n- опциональные строки формата Chrono для обработки выражения. Форматы проверяются в порядке их указания, и возвращается первый успешный результат. Если ни один из форматов не может обработать выражение, возвращается ошибка.
Пример преобразования строки RFC3339:
SELECT to_timestamp_millis('2023-01-31T09:26:56.123456789-05:00');
Результат:
+------------------------------+
| 2023-01-31T14:26:56.123 |
+------------------------------+
Пример: преобразование с произвольными форматами:
SELECT to_timestamp_millis(
'03:59:00.123456789 05-17-2023',
'%c',
'%+',
'%H:%M:%S%.f %m-%d-%Y'
);
Результат:
+------------------------------+
| 2023-05-17T03:59:00.123 |
+------------------------------+
to-timestamp-nanos¶
Преобразует значение в метку времени с наносекундной точностью (YYYY-MM-DDT00:00:00.000000000Z).
Поддерживает строковые и целочисленные типы данных на входе.
Строки обрабатываются как RFC3339 (например, „2023-07-20T05:44:00“), если не указаны форматы Chrono.
Числа интерпретируются как микросекунды с Unix-эпохи (1970-01-01T00:00:00Z).
Возвращает соответствующую метку времени.
to_timestamp_nanos(expression[, ..., format_n])
Аргументы:
expression- выражение для обработки. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.format_n- опциональные строки формата Chrono для обработки выражения. Форматы проверяются в порядке их указания, и возвращается первый успешный результат. Если ни один из форматов не может обработать выражение, возвращается ошибка.
Пример: преобразование строки RFC3339:
SELECT to_timestamp_nanos('2023-01-31T09:26:56.123456789-05:00');
Результат:
+----------------------------------+
| 2023-01-31T14:26:56.123456789 |
+----------------------------------+
Пример: преобразование с указанием форматов:
SELECT to_timestamp_nanos(
'03:59:00.123456789 05-17-2023',
'%c',
'%+',
'%H:%M:%S%.f %m-%d-%Y'
);
Результат:
+----------------------------------+
| 2023-05-17T03:59:00.123456789 |
+----------------------------------+
to_timestamp_seconds¶
Преобразует входные данные в метку времени с точностью до секунд (YYYY-MM-DDT00:00:00.000Z).
Поддерживает строковые и целочисленные типы данных на входе.
Строки обрабатываются как RFC3339 (например, „2023-07-20T05:44:00“), если не указаны форматы Chrono.
Числа интерпретируются как микросекунды с Unix-эпохи (1970-01-01T00:00:00Z).
Возвращает соответствующую метку времени.
to_timestamp_seconds(expression[, ..., format_n])
Аргументы:
expression- выражение для обработки. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.format_n- опциональные строки формата Chrono для обработки выражения. Форматы проверяются в порядке их указания, и возвращается первый успешный результат. Если ни один из форматов не может обработать выражение, возвращается ошибка.
Пример: преобразование строки RFC3339:
SELECT to_timestamp_seconds('2023-01-31T09:26:56.123456789-05:00');
Результат:
+-------------------------+
| 2023-01-31T14:26:56 |
+-------------------------+
Пример: преобразование с указанием форматов:
SELECT to_timestamp_seconds(
'03:59:00.123456789 05-17-2023',
'%c',
'%+',
'%H:%M:%S%.f %m-%d-%Y'
);
Результат:
+-------------------------+
| 2023-05-17T03:59:00 |
+-------------------------+
to_unixtime¶
Преобразует значение в количество секунд, прошедших с Unix-эпохи (1970-01-01T00:00:00Z). Поддерживает строки, даты, метки времени и числа с плавающей точкой в качестве входных данных. Строки обрабатываются как RFC3339 (например, „2023-07-20T05:44:00“), если не указаны форматы Chrono.
to_unixtime(expression[, ..., format_n])
Аргументы:
expression- выражение для обработки. Может быть константой, столбцом или функцией, а также любой комбинацией арифметических операторов.format_n- опциональные строки формата Chrono для обработки выражения. Форматы проверяются в порядке их указания, и возвращается первый успешный результат. Если ни один из форматов не может обработать выражение, возвращается ошибка.
Пример: преобразование строки RFC3339:
SELECT to_unixtime('2020-09-08T12:00:00+00:00');
Результат:
+--------------+
| 1599566400 |
+--------------+
Пример: преобразование с произвольными форматами:
SELECT to_unixtime(
'01-14-2023 01:01:30+05:30',
'%q',
'%d-%m-%Y %H/%M/%S',
'%+',
'%m-%d-%Y %H:%M:%S%#z'
);
Результат:
+--------------+
| 1673638290 |
+--------------+
today¶
Аналог функции current_date.
Функции для работы с массивами¶
array_concat¶
Объединяет указанные массивы в один.
array_concat(array[, ..., array_n])
Аргументы:
array- первый массив для объединения. Можно задать константу, столбец, функцию, а также любую комбинацию операторов для работы с массивами.array_n- последующая колонка или литерал типа массив для объединения.
Пример:
❯ select array_concat([1, 2], [3, 4], [5, 6]);
+---------------------------------------------------+
| array_concat(List([1,2]),List([3,4]),List([5,6])) |
+---------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+---------------------------------------------------+
Примечание
TCS не поддерживает хранение массивов, но разрешает передачу массива в запросе, как в примере выше.
Функции для работы с хешированием¶
digest
md5
sha224
sha256
sha384
sha512
Прочие скалярные функции¶
arrow_cast
arrow_typeof
Встроенные функции TCS¶
Функции наибольшей встречаемости (most occurent)¶
most_occurent_frequency()– количество вхождений наиболее часто встречающегося значения.most_occurent_value()– общее количество записей, удовлетворяющих условиям счетчика.most_occurent_ratio()– отношение значенияmost_occurent_frequency()к значениюmost_occurent_value().Если ни одна транзакция не удовлетворяет условиям счетчика, значение
most_occurent_ratio()равно нулю.Если значение встречается не более одного раза, это значение также равно нулю.
Значение
most_occurent_ratio()всегда находится в интервале[0; 1], поэтому выходной атрибут счетчика должен иметь тип данныхdecimal.
Пример:
При таких вводных данных:
POST http://127.0.0.1:7777/sql -d 'select attr0 as ratio from attributes'
[
{
"ratio": 2
},
{
"ratio": 2
},
{
"ratio": 2
},
{
"ratio": 3
},
{
"ratio": 2
},
{
"ratio": 1
}
]
функции возвращают следующие результаты:
POST http://127.0.0.1:7777/sql -d 'select most_occurent_value(attr0) as res from attributes'
[
{
"res": 2
}
]
POST http://127.0.0.1:7777/sql -d 'select most_occurent_frequency(attr0) as res from attributes'
[
{
"res": 4
}
]
POST http://127.0.0.1:7777/sql -d 'select most_occurent_ratio(attr0) as res from attributes'
[
{
"res": 0.6666666666666666
}
]
Функция distinct_values_ratio()¶
Подсчитывает соотношение уникальных значений во множестве к их общему количеству. Используется, например, для определения различных паттернов фрода.
distinct_values_ratio(array)
Аргументы:
array- множество значений.
Результат:
десятичное число в диапазоне от 0 до 1: чем ближе значение к единице, тем больше уникальных элементов во множестве;
для массива из полностью одинаковых элементов результат равен 0;
для пустого массива, как и для массива из уникальных значений, результат равен 1.
Функция plan_with()¶
Позволяет обращаться к автоматически созданным полям таблицы, например rowid.
Также с помощью аргумента use_index можно влиять на порядок чтения, указывая соответствующие индексы.
Это может быть полезно, поскольку оператор ORDER BY в текущей версии TCS
поддерживается не полностью.
plan_with(table, implicit_fields=true)
Аргументы:
table- имя таблицы.implicit_fields- для доступа к автоматически созданным полям здесь требуется указать значениеtrue(по умолчаниюfalse).use_index- массив индексов, которые планировщик может использовать для запроса.
Пример: обращение к rowid (возвращает значение первого поля).
SELECT * FROM plan_with(t, implicit_fields=true) where rowid = 0
Пример: форсированное задание порядка чтения для индекса.
Для примера рассмотрим таблицу с двумя индексами:
первичный индекс
(a, b, c),вторичный индекс
(d,e)с именемindex_de.
Если нужно читать данные в порядке сортировки по (d,e), то в запросе можно указать
use_index=[index_de]:
SELECT * FROM plan_with(t, use_index=[index_de])
Из всех указанных индексов планировщик выбирает наиболее подходящий (т.е. покрывающий наибольшее количество условий). Если индексы не указаны, то используется первичный индекс, но без префильтрации на уровне индекса (все фильтрации осуществляются уже после чтения).
Функции для работы с IPv4-адресами¶
ipv4_is_in_net()– входит ли IP-адрес в указанную сеть.Аргументы:
network(Utf8) – имя сетиaddress(UInt32) – IP-адрес
Результат:
trueилиfalse(алиасis_in_net)
ipv4_string_to_num– переводит строковое значение IP-адреса в числовое.Аргументы:
ipv4(Utf8) – строковое значение IP-адреса
Результат:
UInt32 (алиас
ipv4_to_num)
ipv4_num_to_string– переводит числовое значение IP-адреса в строковое.Аргументы:
ipv4(UInt32) – числовое значение IP-адреса
Результат:
Utf8 (алиас
ipv4_to_string)
Параметры записи¶
Для команды COPY в TCS можно явным образом задавать параметры записи данных на диск.
Например:
COPY 'attributes'
TO 'res/table_with_attrs'
PARTITIONED BY (col3, col4)
OPTIONS (
format parquet,
compression snappy,
'compression::col1' 'zstd(5)',
)
В этом примере мы записываем все данные из таблицы attributes в директорию
с parquet-файлами. При этом:
Для каждой партиции в запросе в директорию
res/table_with_attrsпараллельно записывается отдельный parquet-файл.Параметр
compression, для которого задано значениеsnappy, указывает, что для всех столбцов нужно использовать кодировщик сжатия форматаsnappy.Параметр
compression::col1переопределяет формат кодировщика для столбцаcol1: для данных из этого столбца в parquet-файле будет использован кодировщик сжатия формата ZSTD с уровнем сжатия 5.
Далее приводится описание всех поддерживаемых параметров записи.
Параметры выполнения запроса¶
Для запросов с командой COPY доступны следующие параметры выполнении запроса:
execution.keep_partition_by_columns¶
Указывает, нужно ли сохранять столбцы в выходных данных при использовании запросов с PARTITIONED BY.
Тип: boolean
Значение по умолчанию: false
Параметры для формата JSON¶
При записи JSON-файлов доступны следующие параметры:
Примечание
Если указан неподдерживаемый параметр, то запрос завершится ошибкой.
СOMPRESSION¶
Формат сжатия для всего JSON-файла.
Поддерживаемые значения: GZIP, BZIP2, XZ, ZSTD и UNCOMPRESSED.
Тип: string
Значение по умолчанию: UNCOMPRESSED
Параметры для формата CSV¶
При записи CSV-файлов доступны следующие параметры:
Примечание
Если указан неподдерживаемый параметр, то запрос завершится ошибкой.
СOMPRESSION¶
Формат сжатия для всего CSV-файла.
Поддерживаемые значения: GZIP, BZIP2, XZ, ZSTD и UNCOMPRESSED.
Тип: string
Значение по умолчанию: UNCOMPRESSED
HEADER¶
Указывает, должен ли CSV-файл содержать заголовки столбцов.
Тип: boolean
Значение по умолчанию: false
DATE_FORMAT¶
Формат, в котором следует кодировать даты в CSV-файле.
По умолчанию для кодирования даты и времени используется формат ISO 8601:
YYYY-MM-DDTHH:mm:ss.sssZ.
Пример значения:
1970-01-01T00:00:00.001.
Тип: string
Значение по умолчанию: YYYY-MM-DDTHH:mm:ss.sssZ
DATETIME_FORMAT¶
Формат, в котором следует кодировать дату и время в CSV-файле.
По умолчанию для кодирования даты и времени используется формат ISO 8601:
YYYY-MM-DDTHH:mm:ss.sssZ.
Пример значения:
1970-01-01T00:00:00.001.
Тип: string
Значение по умолчанию: YYYY-MM-DDTHH:mm:ss.sssZ
TIME_FORMAT¶
Формат, в котором следует кодировать время в CSV-файле.
По умолчанию для кодирования даты и времени используется формат ISO 8601:
YYYY-MM-DDTHH:mm:ss.sssZ.
Пример значения:
1970-01-01T00:00:00.001.
Тип: string
Значение по умолчанию: YYYY-MM-DDTHH:mm:ss.sssZ
RFC3339¶
Если значение равно true, то для кодирования даты и времени используется
формат RFC 339:
YYYY-MM-DDTHH:mm:ss.
Пример значения:
2022-09-27T22:36:00.
Тип: boolean
Значение по умолчанию: false
NULL_VALUE¶
Строка, которую следует использовать для указания нулевых значений в CSV-файле.
Тип: string
Значение по умолчанию: '' (пустая строка)
DELIMITER¶
Символ, который следует использовать в качестве разделителя столбцов в CSV-файле.
Тип: string
Значение по умолчанию: , (запятая)
Параметры для формата Parquet¶
При записи parquet-файлов доступны следующие параметры:
Примечание
Если указан неподдерживаемый параметр, то запрос завершится ошибкой.
Если параметр, специфичный для столбца, указан для столбца, который не существует, то этот параметр будет проигнорирован без ошибок.
Параметры, которые можно указывать для отдельных столбцов, можно задавать с помощью синтаксиса вида
OPTION::COLUMN.NESTED.PATH.
COMPRESSION¶
Используемый кодировщик сжатия и, если применимо, уровень сжатия.
Допустимые значения: uncompressed, snappy, gzip(уровень), lzo,
brotli(уровень), lz4, zstd(уровень) и lz4_raw.
Регистр в указываемых значениях не учитывается.
Если значение равно NULL, то используется кодировщик по умолчанию, заданный в настройках модуля записи parquet-файлов (parquet writer).
Обратите внимание, то данный параметр не задает модуль записи parquet-файлов, используемый по умолчанию.
Можно указывать для столбца: да
Тип: string
Значение по умолчанию: zstd(3)
MAX_ROW_GROUP_SIZE¶
Максимальное количество строк, которые могут быть закодированы в одной группе строк. Для записи и чтения больших групп строк требуется больше памяти.
Можно указывать для столбца: нет
Тип: integer
Значение по умолчанию: 1048576
DATA_PAGESIZE_LIMIT¶
Максимальное ограничение (в байтах) для размера страницы сжатия.
Можно указывать для столбца: нет
Тип: integer
Значение по умолчанию: 1048576
WRITE_BATCH_SIZE¶
Максимальное количество строк, записываемых для каждого столбца в одном пакете (batch).
Можно указывать для столбца: нет
Тип: integer
Значение по умолчанию: 1024
WRITER_VERSION¶
Версия модуля записи parquet-файлов (parquet writer): 1.0 или 2.0.
Можно указывать для столбца: нет
Тип: string
Значение по умолчанию: 1.0
DICTIONARY_PAGE_SIZE_LIMIT¶
Максимальный размер страницы словаря (в байтах).
Можно указывать для столбца: нет
Тип: integer
Значение по умолчанию: 1048576
CREATED_BY¶
Значение свойства «created by» в parquet-файле.
Можно указывать для столбца: нет
Тип: string
Значение по умолчанию: datafusion version 45.0.0
COLUMN_INDEX_TRUNCATE_LENGTH¶
Максимальная длина индекса для столбца.
Можно указывать для столбца: нет
Тип: integer
Значение по умолчанию: 64
DATA_PAGE_ROW_COUNT_LIMIT¶
Максимальное количество строк на странице данных.
Можно указывать для столбца: нет
Тип: integer
Значение по умолчанию: 20000
BLOOM_FILTER_ENABLED¶
Указывает, следует ли записывать bloom-фильтр в parquet-файл.
Можно указывать для столбца: да
Тип: boolean
Значение по умолчанию: false
ENCODING¶
Кодировка, которую следует использовать для parquet-файлов.
Допустимые значения: plain, plain_dictionary, rle, bit_packed,
delta_binary_packed, delta_length_byte_array, delta_byte_array,
rle_dictionary и byte_stream_split.
Регистр в указываемых значениях не учитывается.
Если значение равно NULL, то используется кодировка по умолчанию, заданная в настройках модуля записи parquet-файлов (parquet writer).
См. также параметр DICTIONARY_ENABLED.
Можно указывать для столбца: да
Тип: string
Значение по умолчанию: NULL
DICTIONARY_ENABLED¶
Указывает, включена ли кодировка по словарю. Используйте это значение вместо ENCODING, чтобы задать кодировку по словарю.
Если значение равно NULL, то используется значение по умолчанию, заданное в настройках модуля записи parquet-файлов (parquet writer).
Можно указывать для столбца: да
Тип: boolean
Значение по умолчанию: true
STATISTICS_ENABLED¶
Указывает, включена ли статистика на уровне PAGE или ROW_GROUP.
Допустимые значения: none, chunk и page.
Регистр в указываемых значениях не учитывается.
Если значение равно NULL, то используется значение по умолчанию, заданное в настройках модуля записи parquet-файлов (parquet writer).
Можно указывать для столбца: да
Тип: boolean
Значение по умолчанию: page
MAX_STATISTICS_SIZE¶
Максимальный размер (в байтах), который может занимать статистика.
Можно указывать для столбца: да
Тип: integer
Значение по умолчанию: 4096
BLOOM_FILTER_FPP¶
Вероятность ложного срабатывания (fpp) для bloom-фильтра.
Неявно присваивает BLOOM_FILTER_ENABLED значение true.
Если значение равно NULL, то используется количество по умолчанию, заданное в настройках модуля записи parquet-файлов (parquet writer).
См. также параметр BLOOM_FILTER_NDV.
Можно указывать для столбца: да
Тип: integer
Значение по умолчанию: NULL
BLOOM_FILTER_NDV¶
Количество различных значений (ndv) для bloom-фильтра.
Неявно устанавливает для BLOOM_FILTER_FPP значение true.
Если значение равно NULL, то используется количество по умолчанию, заданное в настройках модуля записи parquet-файлов (parquet writer).
Можно указывать для столбца: да
Тип: integer
Значение по умолчанию: NULL
Аргументы-заполнители (placeholders)¶
В TCS поддерживаются аргументы-заполнители (placeholders) следующих видов:
заполнители в формате PostgreSQL с метками вида
$1, напримерINSERT INTO table(col1, col2) VALUES ($1, $2);заполнители с метками вида
?, напримерINSERT INTO table(col1, col2) VALUES (?, ?).
Типы данных SQL¶
Ниже приводится соответствие типов данных SQL, которые поддерживаются в PostgreSQL, и примитивных типов данных Apache Arrow, которые поддерживаются в TCS.
Типы данных из PostgreSQL нужно использовать в качестве параметров для подготовленных SQL-запросов (prepared statements).
Тип данных в PostgreSQL |
Тип данных в TCS |
Описание |
|---|---|---|
- |
i8 |
8-битное знаковое целое |
int2/smallint |
i16 |
16-битное знаковое целое |
int4/int |
i32 |
32-битное знаковое целое |
int8/bigint |
i64 |
64-битное знаковое целое |
char |
u8 |
8-битное беззнаковое целое |
- |
u16 |
16-битное беззнаковое целое |
- |
u32 |
32-битное беззнаковое целое |
- |
u64 |
64-битное беззнаковое целое |
float4/real |
f32 |
32-битное с плавающей точкой |
float8/double precision/float |
f64 |
64-битное с плавающей точкой |
boolean/bool |
bool |
логический: |
один из int-типов |
ts |
UNIX timestamp в миллисекундах |
varchar/text/string |
utf8 |
строка UTF-8 |
boolean/bool |
bool |
логический: |
один из int-типов |
ts |
UNIX timestamp в миллисекундах |
Сложные типы данных (array, json и т.д.) в текущей версии TCS не поддерживаются.
Примечание
TCS не поддерживает хранение массивов (данных типа array), но разрешает
передачу массива в запросе. См. например функцию array_concat.